如何在单个 SQL CASE 语句中获取多个列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39941887/
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
How to get multiple columns in a single SQL CASE statement?
提问by Michael.Y
I'm trying to get multiple columns(insuredcode, insuredname in this case) from a single CASEstatement.
我正在尝试从单个CASE语句中获取多个列（在本例中为已保险代码、已保险名称）。
The following query has been tried but it concatenates both insuredcode and insuredname as one column.
已尝试使用以下查询，但它将 insurancecode 和 insurancename 连接为一列。
What is the correct syntax to return exactly two columns from such CASEstatement?
从这样的CASE语句中准确返回两列的正确语法是什么？
select
case
when a.policyno[2] in ('E', 'W') then c.insuredcode || c.insuredname
else b.insuredcode || b.insuredname
end
from prpcmain a
left join prpcinsured_1 b on b.proposalno=a.proposalno
left join prpcinsured_2 c on c.proposalno=a.proposalno
where a.policyno in (select policyno from policyno_t);
回答by P?????
A CASEstatement can return only single column not multiple columns
一条CASE语句只能返回单列，不能返回多列
You need two different CASEstatements to do this
你需要两个不同的CASE语句来做到这一点
select
case
when a.policyno[2] in ('E', 'W') then c.insuredcode
else b.insuredcode
end as insuredcode ,
case
when a.policyno[2] in ('E', 'W') then c.insuredname
else b.insuredname
end as insuredname
from prpcmain a
left join prpcinsured_1 b on b.proposalno=a.proposalno
left join prpcinsured_2 c on c.proposalno=a.proposalno
where a.policyno in (select policyno from policyno_t);
回答by Luqman Cheema
It is simply like if/else condition in any language, you can define your condition in Whenstatement and if it is true, SQL executes the Thenstatement, otherwise executes Elsepart, as described below:
就像任何语言中的 if/else 条件一样，您可以在When语句中定义您的条件，如果为真，则 SQL 执行Then语句，否则执行Else部分，如下所述：
Select
CASE
WHEN (cs.ResultSubmitToHOD = 1) THEN 'HOD'
WHEN (cs.ResultSubmitToExamDep = 1) THEN 'Exam'
ELSE 'Teacher'
END AS ResultSubmitStatus
From dbo.CourseSection as cs
回答by sagi
I can suggest something else that might be slightly faster :
我可以建议其他可能稍微快一点的东西：
SELECT s.insuredcode,s.insuredname FROM (
SELECT a.policyno,b.insuredcode,b.insuredname
FROM prpcmain a
left join prpcinsured_1 b on b.proposalno=a.proposalno
WHERE a.policyno[2] not in ('E', 'W')
UNION ALL
SELECT a.policyno,c.insuredcode,c.insuredname
FROM prpcmain a
left join prpcinsured_2 c on c.proposalno=a.proposalno
WHERE a.policyno[2] in ('E', 'W')
) s
where s.policyno in (select policyno from policyno_t);
As to your question, @Prdpshows what you need to do.
至于您的问题，@Prdp显示了您需要做什么。
