时间戳

首页 > 解决方案 > 子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的

sql - 子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的

问题描述

编辑：对不起，伙计们，我最终弄清楚发生了什么。我没有意识到我不能为单个变量设置多个值。我再次修改了第 6 行并使其成为一个简单的 SELECT 函数。查询吐出了我想要的 4 个数字，这是最终目标。

下面的第一个查询计算 4 个数字。我试图弄清楚这四个数字是什么。我尝试在第 6 行删除 COUNT 函数并将其保留为 SELECT 函数，但出现此错误：

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

如果我想让它打印出 4 个数字而不是计数，你知道我需要做什么吗？

原始代码：

DECLARE @startDATE AS DATE = '2019-10-01'
DECLARE @endDATE AS DATE = '2020-09-30'
DECLARE @uicClass AS varchar(50) = '2D'
DECLARE @SNC AS bit = 0

DECLARE @WellViols int = (SELECT COUNT(distinct c.PKey)
    FROM Construct c
        INNER JOIN Well w ON c.WellKey = w.Pkey
        INNER JOIN Compliance cc ON c.PKey = cc.ConstructKey
        INNER JOIN ViolatiON v ON cc.PKey = v.ComplianceKey
        CROSS APPLY (SELECT le.IsIndianCountry
                        FROM well ww
                            INNER JOIN Construct cc ON ww.PKey = cc.WellKey
                            INNER JOIN Loc l ON c.PKey = l.ConstructKey
                            INNER JOIN LocExt le ON l.PKey = le.LocKey
                        WHERE cc.CompletiON = 0 AND cc.SideTrack = 0
                            AND l.LocType = 'surf'
                            AND ww.PKey = w.PKey) l
    WHERE ISNULL(l.IsIndianCountry, 0) = 0
        AND v.SNC = ISNULL(@SNC, 0)
        AND cc.DATEViolatiON BETWEEN @startDATE AND @endDATE
        AND c.ClASs = @uicClass)

修改代码行#6

SELECT DISTINCT c.PKey

标签： sqlsql-servertsql

解决方案

您可以将结果作为逗号分隔列表分配给字符串变量。我不确定您在交叉应用部分中使用的查询返回了什么。如果您可以发布您在查询中获得的结果，或者示例数据和表格脚本，这将更容易获得答案。

 DECLARE @startDATE AS DATE = '2019-10-01'
 DECLARE @endDATE AS DATE = '2020-09-30'
 DECLARE @uicClass AS varchar(50) = '2D'
 DECLARE @SNC AS bit = 0
 DECLARE @WellViols NVARCHAR(MAX) = N''

 SELECT COUNT(distinct c.PKey)
 into #Temp_Table
 FROM Construct c
    INNER JOIN Well w ON c.WellKey = w.Pkey
    INNER JOIN Compliance cc ON c.PKey = cc.ConstructKey
    INNER JOIN ViolatiON v ON cc.PKey = v.ComplianceKey 
    CROSS APPLY (SELECT le.IsIndianCountry
                    FROM well ww
                        INNER JOIN Construct cc ON ww.PKey = cc.WellKey
                        INNER JOIN Loc l ON c.PKey = l.ConstructKey
                        INNER JOIN LocExt le ON l.PKey = le.LocKey
                    WHERE cc.CompletiON = 0 AND cc.SideTrack = 0
                        AND l.LocType = 'surf'
                        AND ww.PKey = w.PKey) l
WHERE ISNULL(l.IsIndianCountry, 0) = 0
    AND v.SNC = ISNULL(@SNC, 0)
    AND cc.DATEViolatiON BETWEEN @startDATE AND @endDATE
    AND c.ClASs = @uicClass)


                 

  SET @WellViols =   STUFF((SELECT DISTINCT ', '+QUOTENAME(  CAST([T2].[PKey] AS 
                VARCHAR(100))) 
               FROM  #Temp_Table [T2]              
               FOR XML PATH('')), 1, 1, '')   

  SELECT @WellViols AS WellViols

   DROP TABLE #Temp_Table

推荐阅读