带有计数和 case 语句的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1538214/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query with count and case statement
提问by Charles Bretana
i need to find how many people have filed (fil_dt) their tax return within 60 days, withing 120 days, within 180 days, over 180 days of their filing period end date (fpe) for 3 different years (2006, 2007, 2008)
我需要找出有多少人在 3 年（2006、2007、2008）的申报期结束日期 (fpe) 的 60 天内、120 天内、180 天内、180 天内提交了 (fil_dt) 纳税申报表
the statement below will give me ALL years i need a count for each year and for each possibility.. anyway i can do this without 2 queries ?
下面的语句将给我所有年份，我需要对每年和每种可能性进行计数..无论如何我可以在没有 2 个查询的情况下做到这一点？
SELECT YEAR(A.FPE) AS "YEAR"
,CASE
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
END AS "NBR MTH"
WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
i need your help thanks a lot
我需要你的帮助非常感谢
回答by Charles Bretana
then write
然后写
SELECT YEAR(A.FPE) AS "YEAR",
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60
THEN 1 Else 0 End) SixtydayCount,
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120
THEN 1 Else 0 End) OneTwentyDayCount,
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180
THEN 1 Else 0 End) OneEightyDayCount,
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180
THEN 1 Else 0 End) OverOneEightyCount
From Table A
WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
Group By YEAR(A.FPE)
If you want the 120 day count and the 180 day count to only include the folks who are over 60 and less than 120, etc. then,
如果您希望 120 天计数和 180 天计数仅包括 60 岁以上和 120 岁以下的人等，那么，
SELECT YEAR(A.FPE) AS "YEAR",
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60
THEN 1 Else 0 End) SixtydayCount,
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) Between 60 And 119
THEN 1 Else 0 End) OneTwentyDayCount,
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) Between 120 And 179
THEN 1 Else 0 End) OneEightyDayCount,
Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) >= 180
THEN 1 Else 0 End) OverOneEightyCount
From Table A
WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
Group By YEAR(A.FPE)
回答by Quassnoi
SELECT CASE
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
END AS NBR_MTH,
y,
COUNT(a.fpe)
FROM (
SELECT 2006 AS y
UNION ALL
SELECT 2007 AS y
UNION ALL
SELECT 2008 AS y
)
LEFT JOIN
A
ON A.FPE >= CAST(CONCAT(y, '-01-01') AS DATETIME)
AND a.FPE < CAST(CONCAT(y + 1, '-01-01') AS DATETIME)
GROUP BY
y, mbr_mth
Unlike the simple GROUP BY YEAR(), this will select 0even for the missing records (like, if there were no 6+records in 2008)
与 simple 不同GROUP BY YEAR()，这0甚至会选择丢失的记录（例如，如果 中没有6+记录2008）
回答by Welbog
Group by the column your case statement represents:
按您的案例陈述所代表的列分组：
SELECT
YEAR(A.FPE) AS "YEAR",
CASE
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
END AS "NBR MTH",
COUNT(1) AS "TOTAL"
FROM Table
WHERE
A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
GROUP BY
"YEAR",
"NBR MTH"
This query will give you a count of every record for each unique year/"NBR MTH" combination.
此查询将为您提供每个唯一年份/“NBR MTH”组合的每条记录的计数。
回答by ercan
Actually, most DBMSs don't allow GROUP BY using aliases, so it must be
实际上，大多数 DBMS 不允许使用别名进行 GROUP BY，因此必须是
...
GROUP BY
YEAR(A.FPE),
CASE
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 '
WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+'
END
