具有计数和条件语句的SQL查询

Question

我需要找出在三个不同的年份（2006、2007、2008）中，在其申报期结束日期（fpe）之后60天内、120天内、180天内以及超过180天内提交了税务申报（fil_dt）的人数。

以下语句将给出所有年份，我需要每年和每种情况的计数...有没有办法在不使用两个查询的情况下完成这个任务？

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'

我需要您的帮助，非常感谢。

- user186425

Answer 1

然后写入

   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)

如果您想让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)

- Charles Bretana

---

总和将会超过 100%：< 60 意味着 < 120 以及更多。我认为 @op 的意思是类似于 从0到60，从61到120 等等。 - Quassnoi

@Quassnoi，是的，我添加了第二个查询来展示如何进行排他计数...但是你是如何在评论中突出显示文本的？请告诉我... - Charles Bretana

@Charles Bretana：就像您在帖子中所做的那样，将文本括在反引号和星号中。 - Quassnoi

我知道这是一个旧答案，但这正是我在寻找的。 - dreamlax

Answer 2

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

与简单的GROUP BY YEAR()不同，这种方法即使在没有记录的情况下也会选择0（例如，如果在2008年中没有6+条记录）。

- Quassnoi

Answer 3

按照你的case语句所代表的列进行分组：

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"

这个查询将为每个唯一的年份/"NBR MTH"组合提供每个记录的计数。

- Welbog

Answer 4

实际上，大多数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

- ercan