SQLJOIN省略了一些欄位

我有以下表格：

Product_T列：
ProductID,
ProductDescription

OrderLine_T列：
OrderID,
ProductID,
OrderedQuantity

Order_T列：
OrderID,
CustomerID,

Customer_T列：
CustomerID,
CustomerName

我想列出產品 ID 和描述，以及購買該產品最多的客戶的客戶 ID 和名稱，并顯示該客戶訂購的總數量。

我想出了以下查詢，以列出每個訂單的最大數量產品：

SELECT o1.OrderID, o1.ProductID, SUM(o1.OrderedQuantity) AS A
FROM OrderLine_T o1
GROUP BY
  o1.ProductID,
  o1.OrderID
HAVING SUM(o1.OrderedQuantity) = (
    SELECT MAX(s.d)
    FROM (
        SELECT
          o1.OrderID,
          o1.ProductID,
          SUM(o1.OrderedQuantity) AS d
        FROM OrderLine_T o1
        GROUP BY
          o1.ProductID,
          o1.OrderID
    ) s
    WHERE o1.ProductID = s.ProductID
)

這給了我一個正確的輸出：

50  20  1
48  17  5
32  14  10
59  13  2
1   10  9
2   8   2
69  7   4
4   6   3
32  5   10
55  4   2
2   3   12
1   2   18
26  1   5

但是，當我嘗試將它與其他表連接時，我可以選擇CustomerNameand CustomerID，如下所示：

SELECT
  o1.ProductID,
  s.CustomerName,
  s.CustomerID,
  SUM(o1.OrderedQuantity) AS A
FROM OrderLine_T o1
INNER JOIN (
    SELECT
      c1.CustomerName,
      c1.CustomerID,
      p1.ProductID
    FROM Product_T p1
    INNER JOIN OrderLine_T o3 ON p1.ProductID = o3.ProductID 
    INNER JOIN Order_T o2 ON o3.OrderID = o2.OrderID
    INNER JOIN Customer_T c1 ON o2.CustomerID = c1.CustomerID
) s ON s.ProductID = o1.ProductID
GROUP BY
  o1.ProductID,
  s.CustomerName,
  s.CustomerID
HAVING SUM(o1.OrderedQuantity) = (
    SELECT MAX(s.d)
    FROM (
        SELECT
          o1.OrderID,
          o1.ProductID,
          SUM(o1.OrderedQuantity) AS d
        FROM OrderLine_T o1
        GROUP BY
          o1.ProductID,
          o1.OrderID
    ) s
    WHERE o1.ProductID = s.ProductID
) ;

輸出縮小為：

17  Contemporary Casuals    1   5
8   Home Furnishings        3   2
7   Eastern Furniture       4   4
10  Eastern Furniture       4   9
20  Dunkins Furniture       8   1
13  Ikards                  13  2

為什么會這樣？

uj5u.com熱心網友回復：

看來您應該在這里使用視窗函式，例如ROW_NUMBER，以及條件聚合

SELECT
  o.ProductID,
  p.Description,
  CustomerID = MAX(CASE WHEN o.rn = 1 THEN c.CustomerID END),
  CustomerName = MAX(CASE WHEN o.rn = 1 THEN c.CustomerName END),
  SUM(CASE WHEN o.rn = 1 THEN o.TotalQty END) AS QtyForTopCustomer
  SUM(o.TotalQty) AS TotalQty
FROM (
    SELECT
      o.ProductID,
      o.CustomerID,
      TotalQty = SUM(oi.OrderedQuantity),
      rn = ROW_NUMBER() OVER (PARTITION BY oi.ProductId ORDER BY SUM(oi.OrderedQuantity) DESC)
    FROM OrderLine_T ol
    INNER JOIN Order_T o ON o.OrderID = ol.OrderID
    GROUP BY
      o.ProductID,
      o.CustomerID
) o
INNER JOIN Customer_T c ON c.CustomerID = o.CustomerID
INNER JOIN Product_T p ON p.ProductID = ol.ProductID
GROUP BY
  o.ProductID,
  p.Description;

如果您只需要該客戶的資料，則可以洗掉條件聚合并僅按行號過濾

SELECT
  o.ProductID,
  p.Description,
  o.CustomerID,
  o.CustomerName,
  o.TotalQty
FROM (
    SELECT
      p.ProductID,
      p.Description,
      o.CustomerID,
      TotalQty = SUM(oi.OrderedQuantity),
      rn = ROW_NUMBER() OVER (PARTITION BY oi.ProductId ORDER BY SUM(oi.OrderedQuantity) DESC)
    FROM OrderLine_T ol
    INNER JOIN Order_T o ON o.OrderID = ol.OrderID
    GROUP BY
      p.ProductID,
      p.Description,
      o.CustomerID
) o
INNER JOIN Customer_T c ON c.CustomerID = o.CustomerID
INNER JOIN Product_T p ON p.ProductID = ol.ProductID
WHERE o.rn = 1;

標籤：sql sql-server 查询语句

上一篇：SQL手冊中關于WHEREIN/EXCEPT子句的澄清說明

下一篇：從日期/時間欄位中選擇周/ISO周數