SQL 中带有别名的多个 Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13630465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple Case Statement in SQL with aliases
提问by user1860212
SELECT DISTINCT AppID,
CASE
WHEN Category = '< 18 years old' THEN 'yes'
ELSE ''
END AS '<18years old',
CASE
WHEN Category = 'SSN Exists' THEN 'yes'
ELSE ''
END AS 'Applicant has SSN',
CASE
WHEN Category = 'Self Employed' THEN 'yes'
ELSE ''
END AS 'Self employed'
FROM Table1
WHERE AppID = 123
OUTPUT DesiredI am trying to produce the results below where each AppID has only 1 line with all the information pertinent to it. The code above produces multiple lines for each application. I tried to put the case statements all together with 1 END, but I get an error when I use Aliases before the END keyword. Thanks
输出期望我试图产生下面的结果，其中每个 AppID 只有 1 行，其中包含与其相关的所有信息。上面的代码为每个应用程序生成多行。我试图将 case 语句与 1 END 放在一起，但是当我在 END 关键字之前使用别名时出现错误。谢谢
AppID <18 Year old Applicant has SSN Self employed
123 yes yes
124 yes yes
125 yes yes
回答by Gordon Linoff
You need a group byrather than a distinct:
你需要一个group by而不是一个distinct：
SELECT AppID,
max(CASE WHEN Category = '< 18 years old' THEN 'yes'
ELSE ''
END) AS '<18years old',
max(CASE WHEN Category) = 'SSN Exists' THEN 'yes'
ELSE ''
END) AS 'Applicant has SSN',
max(CASE WHEN Category = 'Self Employed' THEN 'yes'
ELSE ''
END) AS 'Self employed'
FROM Table1
WHERE AppID = 123
group by AppId
回答by beyond-code
You can nest the Case statements as follows and just alias at the end:
您可以按如下方式嵌套 Case 语句，并在末尾使用别名：
CASE WHEN
Category = '< 18 years old'
THEN 'yes'
ELSE
CASE WHEN
Category = 'SSN Exists'
THEN 'yes'
ELSE
...
...
END
END AS Alias
Is that what you're after?
那是你追求的吗？
回答by xlecoustillier
Your aliases should not be enclosed in ''.
您的别名不应包含在 '' 中。
Check on your DB Engine which type of character you must use to enclose aliases.
检查您的数据库引擎，您必须使用哪种类型的字符来包含别名。
回答by Lamak
If I understand your problem correctly, you need to do a GROUP BY:
如果我正确理解您的问题，您需要执行以下操作GROUP BY：
SELECT AppID,
MAX(CASE
WHEN Category = '< 18 years old' THEN 'yes'
ELSE ''
END) AS '<18years old',
MAX(CASE
WHEN Category = 'SSN Exists' THEN 'yes'
ELSE ''
END) AS 'Applicant has SSN',
MAX(CASE
WHEN Category = 'Self Employed' THEN 'yes'
ELSE ''
END) AS 'Self employed'
FROM Table1
WHERE AppID = 123
GROUP BY AppID
