sql server在group by 中使用 case when

【1】需求

如何统计分类数量?

有些分类要合并统计, 例如: 统计亚洲和欧洲有多少国家,剩下的国家统计到"火星"

　　要求结果是这样的：

　　　　

 

【2】在sql group by 中使用 case when

【2.1】常规正向写法

该部分有误，请忽略

;WITH t1 AS (
SELECT 'Asia' Area,'china' country UNION all
SELECT 'Asia' Area,'russia' country UNION all
SELECT 'europe' Area,'england' country UNION all
SELECT 'europe' Area,'germany' country UNION all
SELECT 'Africa' area,'india' country
)
SELECT CASE WHEN Area='china' THEN 'Asua'
            WHEN Area='russia' THEN ''
            WHEN 
            ELSE 'spark' END AS AreaName,COUNT(1) country_num
            
FROM t1 
GROUP BY CASE WHEN Area='Asia' THEN Area
            WHEN Area='europe' THEN Area
            ELSE 'spark' end

　　

 

 

 这个写法固然可以，但如果不只是亚洲、欧洲，而是有十几个甚至几十个相关项，难道要一个一个 when  Area=value1 when Area=value2......Area=valueN 吗？ 

显示是不合适且冗余复杂的，这个时候就用到我们下面的【2.2】写法

【2.2】反向批量写法

把亚洲欧洲归到一类，其他的归到另一类

;WITH t1 AS (
SELECT 'Asia' Area,'china' country UNION all
SELECT 'Asia' Area,'russia' country UNION all
SELECT 'europe' Area,'england' country UNION all
SELECT 'europe' Area,'germany' country UNION all
SELECT 'Africa' area,'india' country
)
SELECT CASE WHEN Area IN ('Asia','europe') THEN 'Area_other'
            ELSE 'spark' END AS AreaName,COUNT(1) country_num
            
FROM t1 
GROUP BY CASE WHEN Area IN ('Asia','europe') THEN 'Area_other'
            ELSE 'spark' end

　　

 

 

 

 

 或者也可以反过来用 not in

;WITH t1 AS (
SELECT 'Asia' Area,'china' country UNION all
SELECT 'Asia' Area,'russia' country UNION all
SELECT 'europe' Area,'england' country UNION all
SELECT 'europe' Area,'germany' country UNION all
SELECT 'Africa' area,'india' country
)
SELECT CASE WHEN Area NOT IN ('Asia','europe') THEN 'spark'
            ELSE Area END AS AreaName,COUNT(1) country_num
            
FROM t1 
GROUP BY CASE WHEN Area NOT IN ('Asia','europe') THEN 'spark'
            ELSE Area end

　　

【3】关于分数计数的案例

需求：

　　

 

 解决：

WITH t1 AS (
SELECT 'a' as name,100 score UNION all
SELECT 'b' ,99 score UNION all
SELECT 'c' ,80 score UNION all
SELECT 'd' ,59 score UNION all
SELECT 'e' ,40 score
)
select case when score >=90 then '优秀' when score >=80 then '良好' when score >=60 then '及格'  else '不及格' end
,count(1) as num 
FROM t1
group by score

----------------------------------------------------------

;WITH t1 AS (
SELECT 'a' as name,100 score UNION all
SELECT 'b' ,99 score UNION all
SELECT 'c' ,80 score UNION all
SELECT 'd' ,59 score UNION all
SELECT 'e' ,40 score
)
select case when score >=90 then '优秀' when score >=80 then '良好' when score >=60 then '及格'  else '不及格' end
,count(1) as num 
FROM t1
group by case when score >=90 then '优秀' when score >=80 then '良好' when score >=60 then '及格'  else '不及格' end

　　

 

 第二段代码正确，实现了我们的需求