SQL 我可以在 JOIN 条件中使用 CASE 语句吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10256848/
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
Can I use CASE statement in a JOIN condition?
提问by Just a learner
The following image is a part of Microsoft SQL Server 2008 R2 System Views. From the image we can see that the relationship between sys.partitionsand sys.allocation_unitsdepends on the value of sys.allocation_units.type. So to join them together I would write something similar to this:
下图是 Microsoft SQL Server 2008 R2 系统视图的一部分。从图中我们可以看出sys.partitions和之间的关系sys.allocation_units取决于 的值sys.allocation_units.type。所以为了把它们连接在一起，我会写一些类似的东西：
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3)
THEN a.container_id = p.hobt_id
WHEN a.type IN (2)
THEN a.container_id = p.partition_id
END
But the upper code gives a syntax error. I guess that's because of the CASEstatement.
Can anyone help to explain a little?
但是上面的代码给出了一个语法错误。我想那是因为CASE声明。谁能帮忙解释一下？
Add error message:
添加错误信息：
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.
消息 102，级别 15，状态 1，第 6 行“=”附近的语法不正确。
回答by HABO
A CASEexpression returns a value from the THENportion of the clause. You could use it thusly:
甲CASE表达式返回从一个值THEN该条的部分。你可以这样使用它：
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON CASE
WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
ELSE 0
END = 1
Note that you need to do something with the returned value, e.g. compare it to 1. Your statement attempted to return the value of an assignment or test for equality, neither of which make sense in the context of a CASE/THENclause. (If BOOLEANwas a datatype then the test for equality would make sense.)
请注意，您需要对返回的值进行一些处理，例如将其与 1 进行比较。您的语句试图返回赋值或相等性测试的值，这在CASE/THEN子句的上下文中都没有意义。（如果BOOLEAN是数据类型，那么相等性测试就有意义。）
回答by Niranjan Singh
Instead, you simply JOIN to both tables, and in your SELECT clause, return data from the one that matches:
相反，您只需 JOIN 到两个表，并在您的 SELECT 子句中，从匹配的表中返回数据：
I suggest you to go through this link Conditional Joins in SQL Serverand T-SQL Case Statement in a JOIN ON Clause
我建议您通过此链接SQL Server 中的条件连接和JOIN ON 子句中的 T-SQL Case 语句
e.g.
例如
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON a.container_id =
CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
END
Edit: As per comments.
编辑：根据评论。
You can not specify the join condition as you are doing.. Check the query above that have no error. I have take out the common column up and the right column value will be evaluated on condition.
您不能在执行时指定连接条件.. 检查上面没有错误的查询。我已经取出了公共列，并且将根据条件评估正确的列值。
回答by richardtallent
Try this:
尝试这个：
...JOIN sys.allocation_units a ON
(a.type=2 AND a.container_id = p.partition_id)
OR (a.type IN (1, 3) AND a.container_id = p.hobt_id)
回答by DonkeyKong
I think you need two case statements:
我认为你需要两个 case 语句：
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON
-- left side of join on statement
CASE
WHEN a.type IN (1, 3)
THEN a.container_id
WHEN a.type IN (2)
THEN a.container_id
END
=
-- right side of join on statement
CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
END
This is because:
这是因为：
- the CASE statement returns a single value at the END
- the ON statement compares two values
- your CASE statement was doing the comparison insideof the CASE statement. I would guess that if you put your CASE statement in your SELECT you would get a boolean '1' or '0' indicating whether the CASE statement evaluated to True or False
- CASE 语句在 END 处返回单个值
- ON 语句比较两个值
- 您的 CASE 语句正在 CASE 语句内部进行比较。我猜如果你把你的 CASE 语句放在你的 SELECT 中，你会得到一个布尔值“1”或“0”，指示 CASE 语句的计算结果是 True 还是 False
回答by Gont
I took your example and edited it:
我拿了你的例子并编辑了它：
SELECT *
FROM sys.indexes i
JOIN sys.partitions p
ON i.index_id = p.index_id
JOIN sys.allocation_units a
ON a.container_id = (CASE
WHEN a.type IN (1, 3)
THEN p.hobt_id
WHEN a.type IN (2)
THEN p.partition_id
ELSE NULL
END)
回答by Gont
This seems nice
这看起来不错
https://bytes.com/topic/sql-server/answers/881862-joining-different-tables-based-condition
https://bytes.com/topic/sql-server/answers/881862-joining-different-tables-based-condition
FROM YourMainTable
LEFT JOIN AirportCity DepCity ON @TravelType = 'A' and DepFrom = DepCity.Code
LEFT JOIN AirportCity DepCity ON @TravelType = 'B' and SomeOtherColumn = SomeOtherColumnFromSomeOtherTable
回答by Stefan Gabor
Yes, you can. Here is an example.
是的你可以。这是一个例子。
SELECT a.*
FROM TableA a
LEFT OUTER JOIN TableB j1 ON (CASE WHEN LEN(COALESCE(a.NoBatiment, '')) = 3
THEN RTRIM(a.NoBatiment) + '0'
ELSE a.NoBatiment END ) = j1.ColumnName
回答by Sheikh Kawser
Here I have compared the difference in two different result sets:
这里我比较了两个不同结果集的差异：
SELECT main.ColumnName, compare.Value PreviousValue, main.Value CurrentValue
FROM
(
SELECT 'Name' AS ColumnName, 'John' as Value UNION ALL
SELECT 'UserName' AS ColumnName, 'jh001' as Value UNION ALL
SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
SELECT 'Phone' AS ColumnName, NULL as Value UNION ALL
SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
SELECT 'IsActive' AS ColumnName, '1' as Value
) main
INNER JOIN
(
SELECT 'Name' AS ColumnName, 'Rahul' as Value UNION ALL
SELECT 'UserName' AS ColumnName, 'rh001' as Value UNION ALL
SELECT 'Department' AS ColumnName, 'HR' as Value UNION ALL
SELECT 'Phone' AS ColumnName, '01722112233' as Value UNION ALL
SELECT 'DOB' AS ColumnName, '1993-01-01' as Value UNION ALL
SELECT 'CreateDate' AS ColumnName, '2017-01-01' as Value UNION ALL
SELECT 'IsActive' AS ColumnName, '1' as Value
) compare
ON main.ColumnName = compare.ColumnName AND
CASE
WHEN main.Value IS NULL AND compare.Value IS NULL THEN 0
WHEN main.Value IS NULL AND compare.Value IS NOT NULL THEN 1
WHEN main.Value IS NOT NULL AND compare.Value IS NULL THEN 1
WHEN main.Value <> compare.Value THEN 1
END = 1
回答by Kenneth Wilson
Took DonkeyKong's example.
以大金刚为例。
The issue is I needed to use a declared variable. This allows for stating your left and right-hand side of what you need to compare. This is for supporting an SSRS report where different fields must be linked based on the selection by the user.
问题是我需要使用声明的变量。这允许说明您需要比较的左侧和右侧。这是为了支持 SSRS 报告，其中必须根据用户的选择链接不同的字段。
The initial case sets the field choice based on the selection and then I can set the field I need to match on for the join.
初始案例根据选择设置字段选择，然后我可以设置我需要为连接匹配的字段。
A second case statement could be added for the right-hand side if the variable is needed to choose from different fields
如果需要从不同的字段中选择变量，可以在右侧添加第二个 case 语句
LEFT OUTER JOIN Dashboard_Group_Level_Matching ON
case
when @Level = 'lvl1' then cw.Lvl1
when @Level = 'lvl2' then cw.Lvl2
when @Level = 'lvl3' then cw.Lvl3
end
= Dashboard_Group_Level_Matching.Dashboard_Level_Name
