Sqlserver 如何根据自定义逻辑分组

[静坐如禅]

有如下表结构：
 

SELECT * FROM dbo.TB_StudentScore
	ORDER BY Score DESC

 自定义分组逻辑查询:

SELECT 
	(
		CASE 
			WHEN Score>=90 THEN '优秀'
			WHEN Score>=60 or Score>=90 THEN '中等及以上'
			WHEN Score<60 THEN '差'
		END
	) AS LevelInfo,
	COUNT(1) AS LevelCount
FROM dbo.TB_StudentScore
	GROUP BY
	(
		CASE 
			WHEN Score>=90 THEN '优秀'
			WHEN Score>=60 or Score>=90 THEN '中等及以上'
			WHEN Score<60 THEN '差'
		END
	)

查询结果：

 这不是我想要的结果，我想要的是中等及以上包含优秀的那3个，Sqlserver group by之后 数据在A组，就不会在B组出现，
我的诉求是，数据既然属于A组的逻辑，也属于B组的逻辑，就想全部统计到。怎么实现呢？
当然通过union all 可以实现，但代码冗余且复杂。有什么方法能很优雅的，代码简洁的实现如上功能呢？求解答 谢谢。

[幸福感奇差]

在你的查询结果之上再加一层查询，把 levelcount 做一个 cumsum 就行了

• 打赏
• 举报

回复 赞

[yiminglian]

```sql
WITH a
AS (SELECT '小红' student,
           CAST(100.00 AS FLOAT) score,
           GETDATE() createtime
    UNION
    SELECT '小明' student,
           98.00 score,
           GETDATE() createtime
    UNION
    SELECT '张三' student,
           95.50 score,
           GETDATE() createtime
    UNION
    SELECT '王五' student,
           89.00 score,
           GETDATE() createtime
    UNION
    SELECT '李四' student,
           88.00 score,
           GETDATE() createtime
    UNION
    SELECT '坤哥' student,
           75.00 score,
           GETDATE() createtime
    UNION
    SELECT 'jason' student,
           58.50 score,
           GETDATE() createtime
    UNION
    SELECT '铁木真' student,
           49.00 score,
           GETDATE() createtime),
     b
AS (SELECT 90 x,
           100 d,
           '优秀' dy),
     c
AS (SELECT 60 x,
           100 d,
           '中等及以上' dy),
     d
AS (SELECT 0 x,
           59 d,
           '差' dy)
SELECT a.*,b.dy,c.dy,d.dy
FROM a
    LEFT JOIN b
        ON score
           BETWEEN b.x AND b.d
    LEFT JOIN c
        ON score
           BETWEEN c.x AND c.d
    LEFT JOIN d
        ON score
           BETWEEN d.x AND d.d;

![img](https://img-community.csdnimg.cn/images/c91ee26cb3034d88a0c6487070296937.png "#left")

• 打赏
• 举报

回复 赞

[shinger126]

@shinger126 不用union的话，就是下面这个样子 WITH a AS (SELECT '小红' student, CAST(100.00 AS FLOAT) score, GETDATE() createtime UNION SELECT '小明' student, 98.00 score, GETDATE() createtime UNION SELECT '张三' student, 95.50 score, GETDATE() createtime UNION SELECT '王五' student, 89.00 score, GETDATE() createtime UNION SELECT '李四' student, 88.00 score, GETDATE() createtime UNION SELECT '坤哥' student, 75.00 score, GETDATE() createtime UNION SELECT 'jason' student, 58.50 score, GETDATE() createtime UNION SELECT '铁木真' student, 49.00 score, GETDATE() createtime), b as (select '差' Level,0 as minscore,59 as maxscore union all select '中等及以上' Level,60 as minscore,100 as maxscore union all select '优秀' Level,90 as minscore,100 as maxscore) select b.Level,count(*) LevelCount from a join b on a.score between b.minscore and b.maxscore group by b.Level

• 举报

回复 赞

[shinger126]

WITH a
AS (SELECT '小红' student,
CAST(100.00 AS FLOAT) score,
GETDATE() createtime
UNION
SELECT '小明' student,
98.00 score,
GETDATE() createtime
UNION
SELECT '张三' student,
95.50 score,
GETDATE() createtime
UNION
SELECT '王五' student,
89.00 score,
GETDATE() createtime
UNION
SELECT '李四' student,
88.00 score,
GETDATE() createtime
UNION
SELECT '坤哥' student,
75.00 score,
GETDATE() createtime
UNION
SELECT 'jason' student,
58.50 score,
GETDATE() createtime
UNION
SELECT '铁木真' student,
49.00 score,
GETDATE() createtime)
select '优秀',count() LevelCount from a where score>=90
union all
select '中等及以上',count() LevelCount from a where score>=60
union all
select '差',count(*) LevelCount from a where score<60
改用union来处理不就好了，或者像2楼一样定义一个成绩标准表，做关联也行

• 打赏
• 举报

回复 赞