我有以下脚本
SELECT
CASE
WHEN Grade < 4
THEN 'Fail'
WHEN Grade >=4 and Grade < 6
THEN 'C'
WHEN Grade >=6 and Grade < 8
THEN 'Ok'
ELSE 'Exc.'
END AS [Status],
COUNT(Grade) AS v
FROM
GradeList
GROUP BY
[Status]
它看不到列Status
。不知道为什么,有什么问题
这就是SQL Server(和SQL(的定义方式。SELECT
中定义的列别名在FROM
、WHERE
或GROUP BY
子句中不可用。
对此有多种解决方案。我最喜欢使用APPLY
,因为它定义了FROM
子句中的别名:
SELECT v.Status, COUNT(*) as v
FROM GradeList gl CROSS APPLY
(VALUES (CASE WHEN Grade < 4 THEN 'Fail'
WHEN Grade < 6 THEN 'C'
WHEN Grade < 8 THEN 'Ok'
ELSE 'Exc.'
END)
) v(Status)
GROUP BY v.Status;
注意,这也简化了CASE
逻辑。返回第一个匹配条件,因此不需要BETWEEN
。如果您想调整查询,这实际上使添加和删除条件变得更加容易。
SQL Server以特定的逻辑顺序处理SQL Statment。由于SELECT子句位于GROUP BY子句之后,GROUP BY子句看不到列别名。
阅读MSDN 上有关逻辑处理顺序的更多信息
- 来自
- 在
- 联接
- 其中
- 分组依据
- 带立方体或带卷
- 具有
- 选择
- 明显的
- 订购人
- 顶部
您可以使用派生表或通用表表达式来解决此问题。
SELECT Status, COUNT(Grade) AS v
FROM
(
SELECT
CASE
WHEN Grade < 4
THEN 'Fail'
WHEN Grade >=4 and Grade < 6
THEN 'C'
WHEN Grade >=6 and Grade < 8
THEN 'Ok'
ELSE 'Exc.'
END AS [Status],
Grade
FROM
GradeList
) AS g(Status,Grade)
GROUP BY
[Status]
;WITH cte_gradelist(Status,Grade) AS
(
SELECT
CASE
WHEN Grade < 4
THEN 'Fail'
WHEN Grade >=4 and Grade < 6
THEN 'C'
WHEN Grade >=6 and Grade < 8
THEN 'Ok'
ELSE 'Exc.'
END AS [Status],
Grade
FROM
GradeList
)
SELECT Status, COUNT(Grade) AS v
FROM cte_gradelist
GROUP BY
[Status]