SQL Server 2005/2008 条件连接

发布于 2024-10-09 15:09:45 字数 424 浏览 2 评论 0原文

是否有条件连接之类的东西：

SELECT *
FROM TABLE1 A
    IF (a=='TABLE2') THEN INNER JOIN TABLE2 B ON A.item_id=B.id
    ELSE IF (a=='TABLE3') THEN INNER JOIN TABLE3 C ON A.item_id=C.id

虽然 a 是 TABLE1 中的一个字段。

我喜欢在存储过程中使用它，而不使用动态sql（不将查询编写为字符串和 EXEC(@query)）。

编辑：我不能写：

IF (a=='TABLE2) THEN queryA
ELSE IF (a=='TABLE3') THEN queryB

因为 a 是 TABLE1 的一个字段。

原文

Is there such thing like conditional join:

SELECT *
FROM TABLE1 A
    IF (a=='TABLE2') THEN INNER JOIN TABLE2 B ON A.item_id=B.id
    ELSE IF (a=='TABLE3') THEN INNER JOIN TABLE3 C ON A.item_id=C.id

While a is a field in TABLE1.

I like to use this in stored procedures without using dynamic sql (without writing query as string and EXEC(@query)).

EDIT: I can't write:

IF (a=='TABLE2) THEN queryA
ELSE IF (a=='TABLE3') THEN queryB

Because a is a field of TABLE1.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忆悲凉 2024-10-16 15:09:45

编辑：根据下面的评论修改答案：

您可以尝试巧妙地使用一些左连接。这将返回更多列，因此您可能希望比 SELECT * 更具辨别力。

SELECT *
    FROM TABLE1 A
        LEFT JOIN TABLE2 B
            ON A.item_id = B.id
                AND A.a = 'TABLE2'
        LEFT JOIN TABLE3 C
            ON A.item_id = C.id
                AND A.a = 'TABLE3'
    WHERE (B.id IS NOT NULL AND A.a = 'TABLE2')
       OR (C.id IS NOT NULL AND A.a = 'TABLE3')

EDIT: Modified answer based on comment below:

You could try to get clever with some left joins. This will return more columns, so you'd probably want to be more discriminating than just SELECT *.

SELECT *
    FROM TABLE1 A
        LEFT JOIN TABLE2 B
            ON A.item_id = B.id
                AND A.a = 'TABLE2'
        LEFT JOIN TABLE3 C
            ON A.item_id = C.id
                AND A.a = 'TABLE3'
    WHERE (B.id IS NOT NULL AND A.a = 'TABLE2')
       OR (C.id IS NOT NULL AND A.a = 'TABLE3')

回复收藏 0 原文

溺深海 2024-10-16 15:09:45

按要求更新了查询：

SELECT * FROM
(
    SELECT * 
        FROM TABLE1 A  INNER JOIN TABLE2 B 
            ON A.a='TABLE2' --This will eleminate the table rows if the value of A.a is not 'TABLE2' 
         AND A.item_id=B.id) A,
             (SELECT * FROM
             INNER JOIN TABLE3 C 
            ON A.a='TABLE3' --This will eleminate the table rows if the value of A.a is not 'TABLE3'
            AND A.item_id=C.id 
                ) B
) a

Updated the query as requried:

SELECT * FROM
(
    SELECT * 
        FROM TABLE1 A  INNER JOIN TABLE2 B 
            ON A.a='TABLE2' --This will eleminate the table rows if the value of A.a is not 'TABLE2' 
         AND A.item_id=B.id) A,
             (SELECT * FROM
             INNER JOIN TABLE3 C 
            ON A.a='TABLE3' --This will eleminate the table rows if the value of A.a is not 'TABLE3'
            AND A.item_id=C.id 
                ) B
) a

回复收藏 0 原文

~没有更多了~