子查询在MS SQL中返回了超过1个值

问题描述：

SELECT a.SellerID, 
     SUM(TransactionFee) as TransactionFees, 
SUM(Quantity*a.PriceItem) as TransactionValue, 
COUNT(*) as OrdersWithTransactionFees, 
      SUM(Quantity) as Qty, 
      (SELECT SUM(a.Quantity*a.PriceItem) as WholeMonthTransactionValue 
      from BuyProductDetails where SellerID = a.SellerID) as aa 
FROM BuyProductDetails as a 
WHERE MONTH(a.OrderDate)=3 
AND YEAR(a.OrderDate)=2013 
AND TransactionFee IS NOT NULL 
GROUP BY a.SellerID

我有上面的查询......它似乎无法运行。

基本上，我有这张表购买产品详细信息它存储来自不同卖家的所有订单。

一些订单将有TransactionFee。

现在，我需要的是用TransactionFee计算这些订单的总销售额，以及这些卖家的总销售额，包括那些没有TransactionFee的订单。

结果集应具有以下字段：

SellerID

的

总交易费用
销售总额的
带有交易费用的订单数量
数量订购
该卖家的总销售额

但是当我运行该SQL，它返回以下错误：

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

任何帮助深表感谢。谢谢。

我得到了解决方案。这是来自某人的回答，但我现在无法在此页面上看到他的回答。想知道为什么？基本上，只需修改子查询为以下内容：SELECT SUM（b.Quantity * b.PriceItem）作为WholeMonthTransactionValue从BuyProductDetails b其中b.SellerID = a.SellerID – 2013-03-14 07:07:53

答

试过类似的东西？

SELECT a.SellerID, 
    SUM(TransactionFee) as TransactionFees, 
    SUM(Quantity*a.PriceItem) as TransactionValue, 
    COUNT(*) as OrdersWithTransactionFees, 
    SUM(Quantity) as Qty, 
    MIN(a.WholeMonthTransactionValue) as WholeMonthTransactionValue 
    FROM BuyProductDetails as a, 
(SELECT b.SellerID, 
     SUM(b.Quantity*b.PriceItem) as WholeMonthTransactionValue, 
     MONTH(b.OrderDate), 
     YEAR(b.OrderDate) 
FROM BuyProductDetails b 
GROUP BY b.SellerID, 
    MONTH(b.OrderDate) as MonthID, 
    YEAR(b.OrderDate) as YearID) as aa 
    WHERE MONTH(a.OrderDate)=3 
     AND YEAR(a.OrderDate)=2013 
     AND TransactionFee IS NOT NULL 
     AND a.SellerID = aa.SellerID 
     AND MONTH(a.OrderDate)=aa.MonthID 
     AND YEAR(a.OrderDate) = aa.YearID 
    GROUP BY a.SellerID)

答

您可以CASE表达式中使用更有效的选择

SELECT a.SellerID, 
     SUM(CASE WHEN TransactionFee IS NOT NULL THEN TransactionFee END) AS TransactionFees, 
     SUM(CASE WHEN TransactionFee IS NOT NULL THEN Quantity * PriceItem END) AS TransactionValue, 
     COUNT(CASE WHEN TransactionFee IS NOT NULL THEN 1 END) as OrdersWithTransactionFees, 
     SUM(CASE WHEN TransactionFee IS NOT NULL THEN Quantity END) as Qty, 
     SUM(Quantity * PriceItem) AS WholeMonthTransactionValue   
FROM BuyProductDetails AS a 
WHERE MONTH(a.OrderDate) = 3 AND YEAR(a.OrderDate) = 2013 
GROUP BY a.SellerID

演示上SQLFiddle

或者仅仅是在0123添加正确的别名的子查询

SELECT a.SellerID, 
     SUM(TransactionFee) as TransactionFees, 
     SUM(Quantity*a.PriceItem) as TransactionValue, 
     COUNT(*) as OrdersWithTransactionFees, 
     SUM(Quantity) as Qty, 
     (SELECT SUM(d.Quantity * d.PriceItem) 
     FROM BuyProductDetails d 
     WHERE d.SellerID = a.SellerID) as WholeMonthTransactionValue 
FROM BuyProductDetails as a 
WHERE MONTH(a.OrderDate)=3 
AND YEAR(a.OrderDate)=2013 
AND TransactionFee IS NOT NULL 
GROUP BY a.SellerID

演示

子查询在MS SQL中返回了超过1个值

相关推荐