我有以下查詢。我需要將 MODEL_YEAR_FW 2003 到 2022 分組為 3 年
SELECT
COUNT(VEHICLE_FW.MODEL_YEAR_FW) AS COUNT_VEHICLES,
VEHICLES_FW.MODEL_YEAR_FW AS MODEL_YR
FROM
FWUSER.VEHICLES_FW
WHERE
VEHICLES_FW.ARCHIVE_STATUS_FW - 'N'
AND ( (MODEL_YEAR_FW) >2003)
GROUP BY
VEHICLE_FW.MODEL_YEAR_FW
ORDER BY
VEHICLES_FW.MODEL_YEAR_FW
有人請幫助我
uj5u.com熱心網友回復：
您可以使用DENSE_RANK()如下決議函式：
WITH RANKS AS
(
SELECT MODEL_YEAR_FW, ARCHIVE_STATUS_FW,
CEILING(DENSE_RANK() OVER (ORDER BY MODEL_YEAR_FW)/3) GRP
FROM VEHICLES_FW
WHERE ARCHIVE_STATUS_FW = 'N'AND MODEL_YEAR_FW >2003
)
SELECT CONCAT(MIN(MODEL_YEAR_FW), '-', MAX(MODEL_YEAR_FW)) AS MODEL_YR,
COUNT(MODEL_YEAR_FW) AS COUNT_VEHICLES
FROM RANKS
GROUP BY GRP
ORDER BY GRP
查看演示。
uj5u.com熱心網友回復：
您可以使用模運算子%
SELECT VEHICLE_FW.MODEL_YEAR_FW % 3 AS interval3y,
count(*) AS COUNT_VEHICLES,
min(VEHICLES_FW.MODEL_YEAR_FW) AS MODEL_YR
FROM FWUSER.VEHICLES_FW
WHERE VEHICLES_FW.ARCHIVE_STATUS_FW = 'N'
AND MODEL_YEAR_FW >= 2003
GROUP BY interval3y
ORDER BY interval3y
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/507371.html
上一篇：如何從mysql中的字串陣列（即[“12”，“13”，“1”，“2”，“30”.....]）中搜索字串資料（即“12”）？
下一篇：我如何知道SQL表中有多少年？