SQL服务器案例

Question

我想根据称为“类型”的参数从不同的表中进行选择。我可以使用“CASE”从from表中选择吗？

我可以使用这样的东西吗？

select 
a as abc
b as xyz
from ( CASE when @type = 1 then tblSales
            when @type = 2 then tblTransfer
       end
)

Answer 1

我可能会做类似的事情：

SELECT a AS abc, b AS xyz FROM tblSales WHERE @type=1
UNION ALL
SELECT a AS abc, b AS xyz FROM tblTransfer WHERE @type=2

Answer 2

这是您正在寻找的吗？

select
    a as abc,
    b as xyz
from 
    tblSales
WHERE
    @type = 1
UNION
select
    a as abc,
    b as xyz
FROM 
    tblTransfer
WHERE
    @type = 2

Answer 3

如果您想这样做，可以尝试联合（下面的示例），或者您可以使用简单的“if”语句。

您不能在那里使用 case 语句，因为 case 返回单个值，而不是表（这是“from”所期望的）。

create table #tblsales
(
    a varchar(1),
    b varchar(1)
)

create table #tblTransfer
(
    a varchar(1),
    b varchar(1)
)

insert into #tblSales(a,b) values ('s','1')
insert into #tblSales(a,b) values ('s','2')

insert into #tblTransfer(a,b) values ('t','1')
insert into #tblTransfer(a,b) values ('t','2')

declare @type int
set @type=1

select a as abc, b as xyz
from
    (
    select a,b,thetype
    from
        (select a,b, 1 as thetype from #tblsales) sales
        union
        (select a,b, 2 as theType from #tblTransfer)
    ) joined
where theType=@type

Answer 4

Declare @Sql nvarchar(4000)
Declare @type   int
SET @type = 1

SET @Sql = ''

set @Sql = @Sql + '
    select * FROM '
IF @type = 1
BEGIN
    set @Sql = @Sql + '[tblSales]'
END
ELSE
BEGIN
    set @Sql = @Sql + 'tblTransfer'
END

print @sql
exec (@Sql)