使用T-SQL CASE语句汇总报表数据

如果按Agegrp分组,则在选择列表中不能有“年龄”

尝试:

DECLARE @YourTable table (age int, account int)
insert into @YourTable values (1,40)
insert into @YourTable values (2,40)
insert into @YourTable values (3,40)
insert into @YourTable values (4,40)
insert into @YourTable values (5,40)
insert into @YourTable values (6,40)
insert into @YourTable values (7,40)
insert into @YourTable values (8,40)

SELECT
    COUNT(ACCOUNT)AS TOTALCASES, AGEGRP
    FROM (SELECT
              AGE,ACCOUNT, CASE
                               WHEN AGE <=5 THEN 'AGE 0 TO 5'
                               WHEN AGE >=6 THEN 'AGE 6 AND OLDER'
                           END AS AGEGRP
              FROM @YourTable 
         )dt
    GROUP BY AGEGRP

输出:

TOTALCASES  AGEGRP
----------- ---------------
5           AGE 0 TO 5
3           AGE 6 AND OLDER

(2 row(s) affected)

要么执行内部查询(如KM显示),要么重复要分组的表达式:

SELECT 
  AGE,
  COUNT(ACCOUNT) AS TOTALCASES, 
  CASE 
    WHEN AGE <=5 THEN 'AGE 0 TO 5'
    ELSE 'AGE 6 AND OLDER'
  END AS AGEGRP
FROM 
  MAIN 
GROUP BY 
  CASE 
    WHEN AGE <=5 THEN 'AGE 0 TO 5'
    ELSE 'AGE 6 AND OLDER'
  END

在最后的结果集中不可能有年龄。我觉得你把两个要求混在一起了。采用KM的解决方案,您可以获得内部结果,也可以不使用“年龄”列获得外部结果。

edit:km刚刚编辑了他的回复,我也是:)总之,我引用了以下两个结果:

1. 选择年龄(大小写…结束)作为来自主服务器的年龄组
2. 选择AgeGroup,Count(*)作为事例来自(Select Age,(Case…)。结束)按年龄组)按年龄组分组