Mysql 按年份范围 SQL 查询分组

发布于09月13日

我有以下疑问.我需要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

谁来帮帮我

推荐答案

您可以按如下方式使用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

请看demo.