問題是您的查詢正在生成笛卡爾積(類似)結果集。
基本上，對于來自'secondaries'的每個比對行，'main'中的一行會重複多次。
要獲得所需的結果，請一次加入sales表，并比對StoreID和ParentID。
要擷取Individual Sales，僅包含StoreID比對的SUM行，如下所示：
SELECT Store.ID AS `Store ID`
, SUM(IF(main.StoreID = Store.ID,
IF(YEAR(main.SalesDate) = 2012 AND QUARTER(main.SalesDate) = 1,main.SalesAmount,0)
,0)) AS `Individual Sales`
, SUM(
IF(YEAR(main.SalesDate) = 2012 AND QUARTER(main.SalesDate) = 1,main.SalesAmount,0)
) AS `Consolidated Sales`
FROM Store
LEFT JOIN Sales AS `main` ON main.StoreID = Store.ID OR main.StoreID = Store.ParentID
GROUP BY Store.ID
更新 B>
我的錯。 (DOH！)ParentID位于Store表中，而不是Sales表。
上面的查詢不傳回指定的結果。 (正在努力。)
我覺得你已經有了解決方案......
使用ParentID列代替Store表中的ID列。
在ParentID列為NULL的情況下，我們使用ID列中的值。
SELECT IFNULL(Store.ParentID,Store.ID) AS `Store ID`
, SUM(IF(main.StoreID = Store.ID,
IF(YEAR(main.SalesDate) = 2012 AND QUARTER(main.SalesDate) = 1,main.SalesAmount,0)
,0)) AS `Individual Sales`
, SUM(
IF(YEAR(main.SalesDate) = 2012 AND QUARTER(main.SalesDate) = 1,main.SalesAmount,0)
) AS `Consolidated Sales`
FROM Store s
LEFT JOIN Sales AS `main` ON main.StoreID = Store.ID OR main.StoreID = Store.ParentID
GROUP BY IFNULL(Store.ParentID,Store.ID)擊>
此查詢傳回指定的結果集：
SELECT t.StoreID AS `Store ID`
, SUM(IF(t.source='p',
IF(YEAR(t.SalesDate) = 2012 AND QUARTER(t.SalesDate) = 1,t.SalesAmount,0)
,0)) AS `Individual Sales`
, SUM(
IF(YEAR(t.SalesDate) = 2012 AND QUARTER(t.SalesDate) = 1,t.SalesAmount,0)
) AS `Consolodiated Sales`
FROM (
SELECT 'p' AS source
, a.StoreID
, a.SalesDate
, a.SalesAmount
FROM Sales a
UNION ALL
SELECT 's' AS source
, s.ParentID
, b.SalesDate
, b.SalesAmount
FROM Sales b
JOIN Store s ON s.ID = b.StoreID
WHERE s.ParentID IS NOT NULL
) t
GROUP BY t.StoreID
ORDER BY t.StoreID這不是最有效的，内聯視圖(或“派生表”)是Sales表大小的順序。将日期中的謂詞下推到内聯視圖中，或者在派生表中按月或按季度彙總會更有效。
我更有可能将“季度”作為結果集的一部分傳回，以允許我拉出超過四分之一。
SELECT t.StoreID AS `Store ID`
, t.SalesQuarter
, SUM(IF(t.source='p',t.SalesAmount,0)) AS `Individual Sales`
, SUM(t.SalesAmount) AS `Consolodiated Sales`
FROM (
SELECT 'p' AS source
, a.StoreID
, ADDDATE(MAKEDATE(YEAR(a.SalesDate),1), INTERVAL FLOOR(MONTH(a.SalesDate)/4) QUARTER) AS SalesQuarter
, SUM(a.SalesAmount) AS SalesAmount
FROM Sales a
-- WHERE a.SalesDate >= '2012-01-01'
-- AND a.SalesDate < '2012-04-01'
GROUP BY a.StoreID, SalesQuarter
UNION ALL
SELECT 's' AS source
, s.ParentID
, ADDDATE(MAKEDATE(YEAR(b.SalesDate),1), INTERVAL FLOOR(MONTH(b.SalesDate)/4) QUARTER) AS SalesQuarter
, SUM(b.SalesAmount) AS SalesAmount
FROM Sales b
JOIN Store s ON s.ID = b.StoreID
WHERE s.ParentID IS NOT NULL
-- AND a.SalesDate >= '2012-01-01'
-- AND a.SalesDate < '2012-04-01'
GROUP BY s.ParentID, SalesQuarter
) t
GROUP BY t.StoreID, t.SalesQuarter
ORDER BY t.StoreID, t.SalesQuarter