如何在子查询中使用COUNT（）

debugcn 发表于 Dev

萨贾德·约翰逊（Sadjad Johansson）

我的查询会为您提供在日期“ 2014-06-01”和“ 2014-12-31”之间具有销售标题或销售发票的客户。

现在，我想创建一个新的两列，以显示在每个客户获得的日期“ 2014-06-01”和“ 2014-12-31”之间的销售发票和销售抬头。

这里的问题是我不太了解如何思考，但出现此错误

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

这是我的SQL查询。

SELECT t1.Name, /*THIS PART GIVES ME THE ERROR*/ (SELECT COUNT(t5.[Bill-to Customer No_]) as he
                FROM [dbo].[MNO International AB$Sales Header] t5
                WHERE EXISTS
                            (SELECT * FROM [dbo].[MNO International AB$Customer] t6 
                                WHERE t5.[Bill-to Customer No_]=t6.[No_])
                GROUP BY t5.[Bill-to Name]
                ) as TempSa


FROM [dbo].[MNO International AB$Customer] t1
WHERE (EXISTS
         (SELECT *
          FROM [dbo].[MNO International AB$Sales Header] t2
          WHERE t2.[Bill-to Customer No_] =t1.[No_])
       OR EXISTS
         (SELECT *
          FROM [dbo].[MNO International AB$Sales Invoice Header] t3
          WHERE t3.[Posting Date] BETWEEN '2014-06-01' AND '2014-12-31' AND t3.[Bill-to Customer No_] = t1.No_))

这两个查询为我提供了每个客户正确的Sales Header数量以及每个客户的日期之间的发票数量。如何将这两个查询合并到上面查询的select语句中？

    SELECT t1.[Bill-to Name], COUNT(t1.[Bill-to Customer No_]) as Temp
FROM [dbo].[MNO International AB$Sales Header] t1
WHERE EXISTS
            (SELECT * FROM [dbo].[MNO International AB$Customer] t2 
                WHERE t1.[Bill-to Customer No_]=t2.[No_] )
GROUP BY t1.[Bill-to Name]

。

SELECT t1.[Bill-to Name], COUNT(t1.[Bill-to Customer No_]) as Temp
FROM [dbo].[MNO International AB$Sales Invoice Header] t1
WHERE EXISTS
            (SELECT * FROM [dbo].[MNO International AB$Customer] t2 
                WHERE t1.[Bill-to Customer No_]=t2.[No_] AND t1.[Posting Date] BETWEEN '2014-06-01' AND '2014-12-31' AND t2.[No_]='101716' )
GROUP BY t1.[Bill-to Name]

戈登·利诺夫（Gordon Linoff）

您GROUP BY在子查询中有一个。我不确定是否只需要删除它或将它替换为一个相关子句，但这将解决语法错误：

SELECT t1.Name, 
       (SELECT COUNT(t5.[Bill-to Customer No_]) as he
        FROM [dbo].[MNO International AB$Sales Header] t5
        WHERE EXISTS (SELECT 1
                      FROM [dbo].[MNO International AB$Customer] t6 
                      WHERE t5.[Bill-to Customer No_] = t6.[No_]
                     ) 
      ) as TempSa

但是，我认为您想要的价值是：

SELECT t1.Name, 
       (SELECT COUNT(t5.[Bill-to Customer No_]) as he
        FROM [dbo].[MNO International AB$Sales Header] t5
        WHERE EXISTS (SELECT 1
                      FROM [dbo].[MNO International AB$Customer] t6 
                      WHERE t5.[Bill-to Customer No_] = t6.[No_]
                     ) AND
              t5.[Bill-to Name] = t1.Name
--------------^ correlation clause instead of `GROUP BY`
      ) as TempSa

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。