使用具有某些不同值的連接表對數據進行分組

我正在努力弄清楚如何做到這一點，所以請在我解釋時與我裸露。

我們有一個訂單系統，它記錄訂單、訂單行、商品、使用者等。我對這個實例感興趣的是訂單和訂單行。

目前，我們製作包含銷售數據的報告，顯示銷售、退貨等，但速度緩慢且效率低下。這是因為我們使用 PHP/MS SQL Server 查詢單個表，循環遍歷數組，然後將數據組合在一起。

我的任務是嘗試提高效率，因此我嘗試生成一個帶有連接的、別名的、子查詢表的查詢。這些工作正常並結合在一起，但由於我對錶進行分組的方式，我得到了錯誤的值。

請注意，我無法更改任何架構，並且正在使用現有系統。

這個解釋可能看起來很囉嗦，我可能會因為我想問的問題而失去你，但請留在我身邊。

無論如何，這是我目前查詢的架構、範例數據和結果的圖表：

SELECT 
`ol`.`storeID`,
SUM(DISTINCT `o`.`grossValue`) AS 'Total',
SUM(DISTINCT `o`.`paymentValue`) AS 'paymentTotal'
FROM `orders` AS `o`
LEFT JOIN `orderLines` AS `ol` ON `o`.`orderID` = `ol`.`orderID`
WHERE (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY `ol`.`storeID`

我現在在家時在 MySQL 中快速生成了上述內容，我想 SQL Server 的解決方案會類似，但如果我錯了，請糾正我，或者可能為兩者提供解決方案，這將是偉大的！

基本上，訂單有很多行。訂單記錄了行的總價值，以及到目前為止已支付的金額。每個訂單行都是對庫存項目的引用。每行記錄它被記錄到哪個商店，因為不同的項目可能來自不同的商店。我們希望生成報告，顯示每家商店的營業額。

正如您在我的查詢儲存 06 的結果中看到的那樣，顯示的是 4.99，但這應該是 9.98。

正在發生的事情是 netValue 變得與眾不同。我不想要這個。我不想要訂單 <=> 訂單行的重複，但我想要 netValue 的重複，因為這些是單獨的訂單。

我讓你困惑了嗎？

有沒有辦法仍然按 storeID 分組並對 netValue 求和，也許是按 orderID 或 orderLineID 分組？

我嘗試了所有類型的組合，並得到了許多我現在不記得的錯誤，但主要是關於聚合列並且無法分組等。

歡迎所有建議和幫助:)

創建表程式碼：

CREATE TABLE `orderLines` (
 `orderLineID` int(10) NOT NULL AUTO_INCREMENT,
 `orderID` int(10) NOT NULL,
 `itemCode` varchar(30) NOT NULL,
 `netValue` decimal(10,2) NOT NULL,
 `taxValue` decimal(10,2) NOT NULL,
 `storeID` varchar(2) NOT NULL,
 PRIMARY KEY (`orderLineID`)
);



CREATE TABLE `orders` (
 `orderID` int(10) NOT NULL AUTO_INCREMENT,
 `grossValue` decimal(10,2) NOT NULL,
 `paymentValue` decimal(10,2) NOT NULL,
 `typeID` int(10) NOT NULL,
 `orderDate` datetime NOT NULL,
 PRIMARY KEY (`orderID`)
);

預期結果：

+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 02      | 10.00 |  8.00        |
| 06      | 9.98  |  9.98        |
| 07      | 3.00  |  3.00        |
+---------+-------+--------------+

如果可以安全地假設單個訂單只能有一個 distinct ，您可以通過生成一組不同的對並連接該集合而不是表本身StoreID來解決您的問題。這樣您就不需要將 DISTINCT 與聚合一起使用：OrderID, StoreID``orderLines

SELECT 
 `ol`.`storeID`,
 **SUM(`o`.`grossValue`)** AS 'Total',
 **SUM(`o`.`paymentValue`)** AS 'paymentTotal'
FROM
 `orders` AS `o`
LEFT JOIN
 **(SELECT DISTINCT `OrderID`, `StoreID` FROM `orderLines`)** AS `ol`
 ON `o`.`orderID` = `ol`.`orderID`
WHERE
 (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP
 BY `ol`.`storeID`
;

而且，正如已經提到的，您可能應該使用 INNER JOIN 而不是 LEFT JOIN，除非您的訂單沒有訂單行但由於某種原因確實有總價值和付款（有點奇怪，但可能這是有原因的），並且您希望它們包含在結果中。（它們將表示為具有空儲存 ID 的單行。）

刪除DISTINCT. 但是……那你可能會發現結果太大了。原因如下：

考慮為所有 Orders + OrderLines 收集所有數據——每個組合一行。請注意，這將如何多次包含 o.grossValue。

你需要做的是對之前的o.求和。 JOINing``ol

但現在我想知道一些事情。您的架構允許從多個商店完成一個訂單。但是，由於您的數據沒有這樣的範例，我想知道哪個是真的：

• 一個訂單只能來自一個商店。在這種情況下，將 storeID 移動到orders“正確”並且使查詢更簡單。
• 一個訂單可以來自多個商店。在這種情況下，請擴充數據集，以便檢查建議的解決方案。

我認為您不需要“左”。

引用自：https://dba.stackexchange.com/questions/106239