34,838
社区成员




有如下表结构:
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 可以实现,但代码冗余且复杂。有什么方法能很优雅的,代码简洁的实现如上功能呢?求解答 谢谢。
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楼一样定义一个成绩标准表,做关联也行
```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;

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