使用 SQL CASE 语句动态更改要选择的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/492613/
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
Dynamically Changing what table to select from with SQL CASE statement
提问by Robert Iver
I'm trying to write a stored procedure and depending on a certain column value, I want to be able to change what table I select from. I'll try to give an example:
我正在尝试编写一个存储过程，并且根据某个列值，我希望能够更改我从中选择的表。我会试着举一个例子：
SELECT ItemNumber,
ItemType,
Description
FROM
CASE ItemType
WHEN 'A' THEN TableA
ELSE TableB
END
WHERE
CASE ItemType
WHEN 'A' THEN ItemNumber = @itemNumber
ELSE PartNumber = @itemNumber
END
As you can see, not only am I dynamically changing the table I select from, but since these two tables were made at two different times by two different people, the column names are different as well.
如您所见，我不仅动态地更改了从中选择的表，而且由于这两个表是由两个不同的人在两个不同的时间创建的，因此列名也不同。
So, my question is: What is the best way to accomplish this, since SQL Server doesn't seem to like my query I have constructed.
所以，我的问题是：实现此目的的最佳方法是什么，因为 SQL Server 似乎不喜欢我构建的查询。
If anyone who sees what I'm trying to do can suggest a better way to do this, I'd be all ears :-)
如果任何看到我正在尝试做的事情的人都可以提出更好的方法来做到这一点，我会全神贯注:-)
采纳答案by user34850
You can not use CASE statement in FROM clause, but you can use the following instead:
不能在 FROM 子句中使用 CASE 语句，但可以使用以下语句：
SELECT itemnumber, itemtype, description
FROM tablea
WHERE itemnumber = @itemnumber AND itemtype = 'A'
UNION ALL
SELECT itemnumber, itemtype, description
FROM tableb
WHERE partnumber = @itemnumber AND itemtype <> 'A'
回答by Thorsten
I'm not sure why you want to do things in one SQL Statement .. I'm not a SQL Server person, but in an Oracle stored procedure you could write something like this
我不确定你为什么要在一个 SQL 语句中做事情..我不是 SQL Server 人，但在 Oracle 存储过程中你可以写这样的东西
If itemtype = 'A'
Then
<statement for table A>
Else
<statement for Table B>
End if
Something like this should work in SQL Server, too .. maybe someone could expand on this?
像这样的东西也应该在 SQL Server 中工作……也许有人可以对此进行扩展？
回答by axel_c
回答by Cade Roux
You really aren't explaining where ItemTypeis coming from. As suggested UNIONmight be applicable if you are simply combining two tables.
你真的没有解释从哪里来ItemType。UNION如果您只是简单地组合两个表，则建议可能适用。
Here's another possibility which may relate to your problem:
这是另一种可能与您的问题有关的可能性：
SELECT ItemNumber,
ItemType,
COALESCE(TableA.Description, TableB.Description) AS Description
FROM Items
LEFT JOIN TableA
ON Items.ItemType = 'A'
AND TableA.ItemNumber = Items.ItemNumber
LEFT JOIN TableB
ON Items.ItemType <> 'A'
AND TableB.ItemNumber = Items.ItemNumber
回答by Sunny Milenov
You are better of using UNION query to join the tables first, and then SELECT.
您最好先使用 UNION 查询连接表，然后再使用 SELECT。
Also, you may consider creating a view for one of the tables, so it pulls only the columns you need while renaming them, then UNION, and then select from the UNION.
此外，您可以考虑为其中一个表创建一个视图，以便在重命名它们时只提取您需要的列，然后是 UNION，然后从 UNION 中进行选择。
Or use a temp table to store the result from each query. Put the creation of the temp table in a CASE (pseudocode, not tested):
或者使用临时表来存储每个查询的结果。将临时表的创建放在 CASE 中（伪代码，未测试）：
CASE @itemType
WHEN 'A'
SELECT ACol1 AS Col1, ACol2 AS Col2
FROM TABLE_A
INTO #tempTable
WHERE ItemNumber = @itemNumber
ELSE
SELECT BCol1 AS Col1, BCol2 AS Col2
FROM TABLE_B
INTO #tempTable
WHERE PartNumber = @itemNumber
END
SELECT * FROM #tempTable
