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

静坐如禅 2022-07-12 09:23:11

有如下表结构:
 

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 可以实现,但代码冗余且复杂。有什么方法能很优雅的,代码简洁的实现如上功能呢?求解答 谢谢。

...全文
202 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
shinger126 2022-07-12
  • 打赏
  • 举报
回复

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楼一样定义一个成绩标准表,做关联也行

shinger126 2022-07-12
  • 举报
回复
@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
yiminglian 2022-07-12
  • 打赏
  • 举报
回复


```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")
幸福感奇差 2022-07-12
  • 打赏
  • 举报
回复

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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧