在 SQL 语句中使用 CASE WHEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5751038/
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
using CASE WHEN in SQL Statement
提问by userstackoverflow
I have to display information by comparing data in two ID columns, one column has 'ALL' and numbers as ID's while the other has only numbers in it as ID's. My problem is, I cannot compare character and number columns with a number column. So I am using CASE WHEN.
我必须通过比较两个 ID 列中的数据来显示信息，一列有“ALL”和数字作为 ID，而另一列只有数字作为 ID。我的问题是，我无法将字符和数字列与数字列进行比较。所以我使用 CASE WHEN。
If the value is 'ALL' then display 'ALL' in the output, else display name for the matching records.
如果值为“ALL”，则在输出中显示“ALL”，否则显示匹配记录的名称。
Here is the code:
这是代码：
CASE
WHEN secorg.org_id = 'ALL' THEN 'ALL'
WHEN secorg.org_id = progmap.org_id THEN secorg.org_name
END AS org_name,
the condition is this: 'secorg.org_id = progmap.org_id' which is based on the id and I have to display secorg.org_name if the id's are same.
条件是这样的：'secorg.org_id = progmap.org_id' 这是基于 id 的，如果 id 相同，我必须显示 secorg.org_name。
Here is the entire query:
这是整个查询：
SELECT distinct program_id,
prog_name,
case
when Eitc_Active_Switch = '1' then 'ON'
when Eitc_Active_Switch = '0'then 'OFF'
End as Prog_Status,
progmap.client_id,
case
when secorg.org_id = 'ALL' then 'ALL'
--when secorg.org_id = progmap.org_id then secorg.org_name
else secorg.org_name
end as org_name,
case
when prog.has_post_calc_screen = 'True' then 'True'
Else 'False'
End as Referal_ID,
case
when progmap.program_ID IN ( 'AMC1931', 'AMCABD', 'AMCMNMI',
'AMC' )
And sec.calwinexists_ind = '1' then 'Yes'
when progmap.program_ID IN ( 'AMC1931', 'AMCABD', 'AMCMNMI',
'AMC' )
And sec.calwinexists_ind = '0'then 'No'
when progmap.program_ID NOT IN (
'AMC1931', 'AMCABD', 'AMCMNMI', 'AMC' ) then
'N/A'
End as calwin_interface,
sec.Client_name
FROM ref_programs prog (nolock)
LEFT OUTER JOIN ref_county_program_map progmap (nolock)
ON progmap.program_id = prog.prog_id
AND progmap.CLIENT_ID = prog.CLIENT_ID
INNER join sec_clients sec (nolock)
on sec.client_id = progmap.Client_id
Inner join sec_organization secorg (nolock)
on secorg.org_id = progmap.org_id
回答by Ryan
Why not cast the number columns to varchar columns?
为什么不将数字列转换为 varchar 列？
If you're using SQL SERVER you can do that like so:
如果您使用的是 SQL SERVER，您可以这样做：
CONVERT(VARCHAR,secorg.org_id) = CONVERT(VARCHAR,progmap.org_id)
You'll have to do an outer join for instances when the column that is both 'ALL' and numbers is 'All' as it won't be able to inner join to the other table.
对于同时为 'ALL' 和 numbers 的列是 'All' 的情况，您必须进行外部连接，因为它将无法内部连接到另一个表。
For the quick fix based on your code above you can just change the second WHEN clause to look like so (again assuming you're using MS SQL SERVER):
对于基于上面代码的快速修复，您只需将第二个 WHEN 子句更改为如下所示（再次假设您使用的是 MS SQL SERVER）：
WHEN CONVERT(VARCHAR,secorg.org_id) = CONVERT(VARCHAR,progmap.org_id) THEN secorg.org_name
Try this as your query:
试试这个作为你的查询：
SELECT DISTINCT
program_id,
prog_name,
CASE Eitc_Active_Switch
WHEN '1' THEN 'ON'
ELSE 'OFF'
END AS Prog_Status,
progmap.client_id,
ISNULL(secorg.org_name,'ALL') AS org_name,
CASE prog.has_post_calc_screen
WHEN 'True' THEN 'True'
ELSE 'False'
END AS Referal_ID,
CASE WHEN progmap.program_ID IN ('AMC1931','AMCABD','AMCMNMI','AMC') AND sec.calwinexists_ind = '1' THEN
'Yes'
WHEN progmap.program_ID IN ('AMC1931','AMCABD','AMCMNMI','AMC') AND sec.calwinexists_ind = '0' THEN
'No'
WHEN progmap.program_ID NOT IN ('AMC1931','AMCABD','AMCMNMI','AMC') THEN
'N/A'
END AS calwin_interface,
sec.Client_name
FROM
ref_programs prog (nolock)
LEFT OUTER JOIN ref_county_program_map progmap (nolock) ON progmap.program_id = prog.prog_id AND progmap.CLIENT_ID = prog.CLIENT_ID
INNER JOIN sec_clients sec (nolock) ON sec.client_id = progmap.Client_id
LEFT OUTER JOIN sec_organization secorg (nolock) ON CONVERT(VARCHAR,secorg.org_id) = CONVERT(VARCHAR,progmap.org_id)
回答by Conrad Frix
The case statement is fine its the field alias thats bad it shoud be
case 语句很好，它的字段别名应该是坏的
END As org_name
A multipart alias like secorg.org_name won't work
像 secorg.org_name 这样的多部分别名将不起作用
