问题是您的查询正在生成笛卡尔积(类似)结果集。
基本上，对于来自'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