SQL 基于两个表的select CASE语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33742036/
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
select CASE statement based on two tables
提问by Minott Opdyke
MS SQL Server 2008R2 Management Studio
MS SQL Server 2008R2 管理工作室
I am running a SELECTon two tables. I'll simplify it to the part where I'm having trouble. I need to modify the SELECTresults to a certain format for a data import. My CASEstatement works fine until I get to the point that I need to base the WHEN ... THEN...on a different table column
我SELECT在两张桌子上运行一个。我会把它简化到我遇到麻烦的部分。我需要将SELECT结果修改为某种格式以进行数据导入。我的CASE语句工作正常，直到我需要WHEN ... THEN...基于不同的表列
TABLE1
-----------------
name | tag | code
-----------------------
name1 | N | 100
name2 | N | 100
name3 | N | 200
name4 | Y | 100
name5 | N | 400
name6 | N | 700
CODES
-------------------------
code | desc
-------------------------
100 | string1
200 | string2
300 | string2
400 | string2
700 | string2
SELECT name,
Case CODES.desc
when 'string1' then 'String 1'
when 'string2' then 'String 2'
when 'string3' then 'String 3'
when 'string4' then 'String 4'
END as description
FROM TABLE1
join CODES on TABLE1.code = CODES.code
This works fine. The problem is if TABLE1.tag = Y, then description needs to be 'Other string'which is not in the CODEStable
这工作正常。问题是如果TABLE1.tag = Y，那么描述需要'Other string'是CODES表中没有的
I tried adding:
我尝试添加：
Case CODES.desc
.....
when TABLE1.tag = Y then CODES.desc 'Other String'
but it didn't work.
但它没有用。
采纳答案by Steve
You were close but I think this is what you are looking for. The fact that they are in different tables really doesn't matter to the CASE, just the JOIN:
你很接近，但我认为这就是你正在寻找的。他们在不同的表中的事实对 CASE 并不重要，只是 JOIN：
SELECT name,
Case WHEN Table1.tag = 'Y'
then CODES.Desc
ELSE 'Other String'
END as description
FROM TABLE1
join CODES on TABLE1.code = CODES.code
回答by Mureinik
You can use the slightly more robust casesyntax and express the cases as conditions instead of just possible values:
您可以使用稍微更健壮的case语法并将情况表示为条件，而不仅仅是可能的值：
SELECT name,
CASE WHEN table1.tag = 'Y' THEN 'other string'
WHEN codes.[desc] = 'string1' THEN 'String 1'
WHEN codes.[desc] = 'string2' THEN 'String 2'
WHEN codes.[desc] = 'string3' THEN 'String 3'
WHEN codes.[desc] = 'string4' THEN 'String 4'
END AS description
FROM table1
JOIN codes ON table1.code = codes.code
回答by Rabbit
I can't comment on a post yet. But why have a code table with a code description if you're going to change the description anyways? Instead, you should just modify the current description in that table or add a column with the secondary description you need. Then the case statement is a lot less complex.
我还不能对帖子发表评论。但是，如果您无论如何都要更改描述，为什么还要有一个带有代码描述的代码表呢？相反，您应该只修改该表中的当前描述或添加一个包含您需要的辅助描述的列。那么 case 语句就不那么复杂了。
CASE WHEN TABLE1.tag = 'Y'
THEN 'Other String'
ELSE CODES.other_desc
END AS description
