我可以在 JOIN 条件中使用 CASE 语句吗？

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

请注意，您需要对返回的值执行某些操作，例如将其与 1 进行比较。您的语句试图返回赋值的值或测试相等性，这在 CASE/THEN 的上下文中都没有意义条款。 （如果 BOOLEAN 是一个数据类型，那么相等性测试就有意义了。）

相反，您只需加入两个表，并在您的 SELECT 子句中，从匹配的表中返回数据：

我建议您通过此链接 Conditional Joins in SQL Server 和 T-SQL Case Statement in a JOIN ON Clause

例如

    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 

编辑：根据评论。

你不能像你一样指定连接条件。检查上面没有错误的查询。我已取出公共列，并将根据条件评估右列值。

尝试这个：

...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)

我认为您需要两个案例陈述：

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             

这是因为：

CASE 语句在 END 处返回单个值

ON 语句比较两个值

您的 CASE 语句正在 CASE 语句内部进行比较。我猜如果你把你的 CASE 语句放在你的 SELECT 中，你会得到一个布尔值“1”或“0”，表明 CASE 语句是评估为 True 还是 False

是的你可以。这是一个例子。

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 

我拿了你的例子并编辑了它：

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)

这看起来不错

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

在这里，我比较了两个不同结果集的差异：

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 

根据条件，至少有两种加入方式。一个比另一个快：

declare @loopZaKosovnice int = 1
select * 
from tHE_MoveItem mi 
left join tHE_SetProdSt st on st.acIdent = mi.acIdent

-- slow
--join the_setitem si on si.acident = case when @loopZaKosovnice = 0 then mi.acident else st.acIdentChild end 

-- two times as fast
left join the_setitem si1 on @loopZaKosovnice = 0 and si1.acident = mi.acident
left join the_setitem si2 on @loopZaKosovnice = 1 and si2.acident = st.acIdentChild
join the_setitem si on si.acident = isnull (si1.acident, si2.acIdent)

以大金刚为例。

问题是我需要使用声明的变量。这允许说明您需要比较的左侧和右侧。这是为了支持 SSRS 报告，其中必须根据用户的选择链接不同的字段。

初始案例根据选择设置字段选择，然后我可以设置我需要匹配的字段以进行连接。

如果需要从不同字段中选择变量，则可以在右侧添加第二个 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