带别名的Case函数

Question

我希望使用带有别名的CASE语句，结果为两列的商。我写的代码如下，但它返回一个错误。有人能帮我一下吗?

SELECT TOP 5(ROUND(Registered_Students/Total_Student * 100,2)) AS Porcentaje, C.Total_Student, C.Registered_Students, S.Subject_Name, DATEPART(Year, C.Date) AS Año,
CASE WHEN Porcentaje >= 75 THEN 'Elected'
WHEN Porcentaje >= 50 THEN 'Elected 1'
ELSE 'Not elected' AS Elections
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
ORDER BY Porcentaje

Answer 1

SQL server case语句语法错误:

SELECT column1,
column2,
CASE WHEN CONDITION THEN 'Value1'
ELSE 'Value2' END AS columnX
FROM table

你只需要在你的语法中添加END就可以了

Answer 2

你必须插入单词;'END'行结束

SELECT C.Total_Student, C.Registered_Students, S.Subject_Name, DATEPART(Year, C.Date) AS Año,
CASE WHEN Porcentaje >= 75 THEN 'Elected'
WHEN Porcentaje >= 50 THEN 'Elected 1'
ELSE 'Not elected' END AS Elections
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
ORDER BY Porcentaje

按原样

ELSE 'Not elected' AS Elections

将来

ELSE 'Not elected' END AS Elections

Answer 3

创建列别名时，不能在同一SELECT子句中引用它。有一个派生表(即FROM子句中的子查询)，在其中创建Porcentaje列。

SELECT TOP 5 dt.*,
CASE WHEN Porcentaje >= 75 THEN 'Elected'
WHEN Porcentaje >= 50 THEN 'Elected 1'
ELSE 'Not elected'
END AS Elections
FROM
(
select
(ROUND(Registered_Students/Total_Student * 100,2)) AS Porcentaje,
C.Total_Student, C.Registered_Students, S.Subject_Name, DATEPART(Year, C.Date) AS Año
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
) dt
ORDER BY Porcentaje

Answer 4

标准大小写语法为

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

你可以像这样修改你的查询

• 查询将更容易理解
• 不同的缩进使得
更容易修改和阅读

SELECT TOP 5
(ROUND(Registered_Students / Total_Student * 100, 2)) AS Porcentaje
, C.Total_Student
, C.Registered_Students
, S.Subject_Name
, DATEPART(YEAR, C.Date) AS Año,
(CASE
WHEN Porcentaje >= 75 THEN 'Elected'
WHEN Porcentaje >= 50 THEN 'Elected 1'
ELSE 'Not elected' END) AS Elections
FROM Cohort AS C
INNER JOIN Subject AS S
ON S.Id_Subject = C.Id_Subject
ORDER BY Porcentaje