我可以编写一个有条件表选择的查询

We have 2 tables with identical structure and based on a variable I want to choose which table to select on with out having to write 2 queries in my procedure.

我们有两个具有相同结构的表,并且基于一个变量,我想选择要选择的表,而不必在我的过程中编写2个查询。

Is this possible?

这可能吗?

I tried

declare @table int
set @table = 1 

Select orderID, Quantity 
from case when @table = 1 then tblOrders else tblSubscriptionOrders end
where filled = 0

But that did not work

但那没用

4 个解决方案

#1

You would need to use dynamic SQL for this (assuming you want to scale it to more than just 2 tables), which would work but is suboptimal as SQL will not generate statistics for it and have a harder time optimizing the query.

您需要为此使用动态SQL(假设您希望将其扩展到超过2个表),这将起作用但是不是最理想的,因为SQL不会为其生成统计信息并且更难以优化查询。

declare @table sysname
declare @SQL varchar(1000)

set @table = 'MyTable'
SET @SQL='SELECT orderID, Quantity FROM ' + QUOTENAME(@table) + ' WHERE filled=0'

exec sp_executesql @SQL

or, in a stored procedure:

或者,在存储过程中:

CREATE PROCEDURE p_ConditionalSelect @table sysname
as

declare @SQL varchar(1000)

set @table = 'MyTable'
SET @SQL='SELECT orderID, Quantity FROM ' + QUOTENAME(@table) + ' WHERE filled=0'

exec sp_executesql @SQL

#2

If it's just two tables you could do:

如果它只是两个表你可以做:

Declare @table = 1

SELECT *
FROM Table1
WHERE <stuff>
AND @Table = 1

UNION ALL

SELECT *
FROM Table2
WHERE <stuff>
AND @Table = 2

The filter on @table will result in only one of the two halves showing data.

@table上的过滤器只会导致显示数据的两半中的一半。

#3

One option is to use Dynamic SQL but if performance isn't an immediate issue, much simpler is to just UNION the tables and add a dummy [table] column to select from.

一种选择是使用动态SQL,但如果性能不是一个直接问题,那么只需UNION表并添加一个虚拟[table]列来进行选择就更简单了。

SELECT orderID, Quantity
FROM   (
  SELECT [table] = 1, orderID, Quantity
  FROM   tblOrders
  UNION ALL
  SELECT [table] = 2, orderID, Quantity
  FROM   tblSubscriptionOrders
  ) t
WHERE t.Table = @table

#4

You can try this

你可以试试这个

declare @table int
set @table = 1 

Select orderID, Quantity 
From tblOrders 
Where @table = 1 
And  filled = 0

UNION ALL

Select orderID, Quantity 
From tblSubscriptionOrders 
Where @table = 2
And  filled = 0

or this:

declare @table int
set @table = 1 

if @table = 1
    Select orderID, Quantity 
    From tblOrders 
    Where filled = 0
else if @table = 2
    Select orderID, Quantity 
    From tblSubscriptionOrders 
    Where filled = 0

#1