我可以在JOIN条件下使用CASE语句吗? [英] Can I use CASE statement in a JOIN condition?

问题描述

下图是Microsoft SQL Server 2008 R2系统视图的一部分.从图像中我们可以看到sys.partitions和sys.allocation_units之间的关系取决于sys.allocation_units.type的值.因此，将他们聚在一起，我会写一些类似的东西:

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.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to join them together I would write something similar to this:

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 

但是上面的代码给出了语法错误.我想那是因为CASE语句. 有人可以帮忙解释一下吗?

But the upper code gives a syntax error. I guess that's because of the CASE statement. Can anyone help to explain a little?

添加错误消息:

信息102，级别15，状态1，第6行在'='附近的语法不正确.

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near '='.

推荐答案

CASE表达式从子句的THEN部分返回一个值.您可以这样使用它:

A CASE expression returns a value from the THEN portion of the clause. You could use it thusly:

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是数据类型，则对相等性的测试将是有意义的.)

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/THEN clause. (If BOOLEAN was a datatype then the test for equality would make sense.)

这篇关于我可以在JOIN条件下使用CASE语句吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！