TSQL 根据 CASE 值从不同的表中选择 [英] TSQL Select from Different Table based on a CASE value

问题描述

我在 SQL Server 2012 上运行.

I'm running on SQL Server 2012.

想象一下，我有 3 个表(Table1、Table2、Table3)，它们都有共同的 2 个字段(ID、WhereParam).

Imagine I have 3 tables (Table1, Table2, Table3) that all have in common 2 fields (ID, WhereParam).

实现这一目标的正确方法是什么?

What would be the right way to achieve this?

SELECT ID FROM 
            (CASE @Var  
                WHEN "AA" THEN Table1  
                WHEN "BB" THEN Table2  
                ELSE Table3)
      WHERE "TableSelected".WhereParam = @WhereParam

推荐答案

使用IF(正确的方式)

IF @Var = 'AA'
BEGIN
    SELECT ID FROM Table1
    -- WHERE ...
END
ELSE IF @Var = 'BB'
BEGIN
    SELECT ID FROM Table2 
    -- WHERE ...
END
ELSE
BEGIN
    SELECT ID FROM Table3  
    -- WHERE ...
END

其他方式(你应该避免，但我会发布以供参考)

Other ways (you should avoid but I'll post for reference)

使用动态 SQL

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'SELECT ID FROM ' + CASE @Var WHEN 'AA' THEN 'Table1' 
                                            WHEN 'BB' THEN 'Table2' ELSE 'Table3' END + ' -- WHERE ...'
EXEC(@sql)

使用UNION

SELECT Id
FROM Table1
WHERE @Var='AA'
-- AND ...

UNION ALL

SELECT Id
FROM Table2
WHERE @Var='BB'
-- AND ...

UNION ALL

SELECT Id
FROM Table3
WHERE @Var NOT IN('AA', 'BB')
-- AND ...

这篇关于TSQL 根据 CASE 值从不同的表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！