主页 / dba / 问题 / 212609
Accepted
heye
Asked: 2018-07-19 16:18:05 +0800 CST

case语句子查询需要返回多个值

  • 772

下面的查询需要返回多个值

select  

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject 
      WHERE StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id)
ELSE (SELECT Stationery.ImpFile from Stationery 
      WHERE Stationery.Stationery_Id = [Order].Stationery_Id)
END AS ImpFile

FROM [Order]
LEFT OUTER JOIN ClientProject ON ClientProject.ClientProject_Id = [Order].ClientProject_Id
LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

但它显示了一个错误

消息 512，级别 16，状态 1，第 1 行子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

不知道如何处理。任何帮助将不胜感激

sql-server subquery

2 个回答

  1. Best Answer

    这应该有效：

    select  

CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
    THEN StationeryClientProject.ImpFile
ELSE Stationery.ImpFile
END AS ImpFile

FROM [Order]
    LEFT OUTER JOIN ClientProject ON   ClientProject.ClientProject_Id = [Order].ClientProject_Id
    LEFT OUTER JOIN StationeryClientProject ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
    LEFT OUTER JOIN Stationery ON Stationery.Stationery_Id = [Order].Stationery_Id

    错误消息是由在 case 语句中返回的多行引起的。由于您已经在 FROM 子句中返回了所需的行，因此您可以简单地在 case 语句中引用所需的列。

    • 2

  2. 在像您这样的标量上下文中使用的子查询CASE-表达式必须返回单个（标量）值， - 也应该返回单个值。很难猜出你想要什么，因为你没有用人类语言解释你的问题，也没有提供表结构或示例数据，但我认为，既然你已经加入了所有必要的表，你可能想要这样的东西：

    SELECT  
  CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
   THEN StationeryClientProject.ImpFile
   ELSE Stationery.ImpFile 
  END AS ImpFile
FROM [Order]
  LEFT OUTER JOIN ClientProject 
    ON ClientProject.ClientProject_Id = [Order].ClientProject_Id
  LEFT OUTER JOIN StationeryClientProject 
    ON StationeryClientProject.ClientProject_Id = [Order].ClientProject_Id 
  LEFT OUTER JOIN Stationery 
    ON Stationery.Stationery_Id = [Order].Stationery_Id
    • 0

相关问题