在SQL查询语句的情况下(Case Statement in SQL Query)

我试图在SQL Server 2005中使用case语句select语句是这样，我也得到了错误“只有一个表达式可以在选择列表时，不引入子查询与EXISTS指定。” 不能的情况进行SQL查询内部使用？

declare @TypeofDayID int
set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID)


Select CASE 
    WHEN @TypeofDayID = 1 
        THEN (Select * from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID)
    WHEN @TypeofDayID = 2
        THEN (Select * from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID)
    WHEN @TypeofDayID = 9 or @TypeofDayID = 10
        THEN (Select * from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID)
    END
from RepInfo RD

Answer 1:

CASE不用于逻辑流控...使用IF / ELSE IF代替：

declare @TypeofDayID int
set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID)

IF @TypeofDayID = 1 
  Select * 
  from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID
ELSE IF @TypeofDayID = 2
  Select * 
  from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID
ELSE IF @TypeofDayID = 9 or @TypeofDayID = 10
  Select * 
  from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID

记住...因为你正在使用SELECT * ，并加入到基于不同的表@TypeOfDayID ，你很可能会最终有一个锯齿状的结果集，这意味着你必须根据不同数量的列该分支被采用。

这可以通过编程工作痛苦，所以这将是避免一个好主意SELECT *因为这个原因，还有其他原因？

Answer 2:

我认为你正在试图做可以更好地处理这样的东西。

declare @TypeofDayID int
set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID)

IF @TypeofDayID = 1 
    Select * 
    from RepInfo RD 
    inner join SellingInfo S on S.RepInfoID = @RepInfoID
ELSE IF @TypeofDayID = 2
    Select * 
    from RepInfo RD 
    inner join UpgradingInfo U on U.RepInfoID = @RepDailyID
ELSE IF @TypeofDayID = 9 or @TypeofDayID = 10
    Select * 
    from RepInfo RD 
    inner join DeliveryInfo D on D.RepDailyID = @RepDailyID

文章来源: Case Statement in SQL Query