34,576
社区成员
发帖
与我相关
我的任务
分享
select TypeName,count(*)
(select *,(case when (TempVal>=12.0 and TemValue<=15.9) THEN '良好' when (TempVal>=16.0 and TemValue<=21.9) Then '优秀'
else '一般') TypeName as
from tab) as a group by TypeName
WITH --用WITH来写更符合我的思考方式
--结果集仅要求精确分组出优秀和良好的,其余统统归类为其他
T1 AS
(
SELECT * FROM Temp WHERE TypeName IN ('优秀','良好')
),
--联合查询,将每一个人的分数转换成等级评价
T2 AS
(
SELECT
UserTemp.*,
ISNULL(T1.TypeName,'其他') AS TypeName
FROM
UserTemp LEFT JOIN T1 ON UserTemp.TempVal BETWEEN T1.TempStar AND T1.TempEnd
)
--聚合统计
SELECT TypeName,COUNT(Id) AS nber FROM T2 GROUP BY TypeName