mysql - 在 MySQL 中，如何使用表 A 将两个表与其自身建立一对多关系？

Question

我有两张 table 。

“商店”表是一个人口统计表，其中包含如下字段:

ID, ParentID, ConsolidationType

ID 是给定商店的唯一标识符。

ParentID 可以为 NULL，或者如果其 ConsolidationType 为“Secondary”，则包含其父存储的 ID。

ConsolidationType 为 NULL、“Primary”或“Secondary”。

“销售”表具有如下字段:

StoreID, SalesDate, SalesAmount

StoreID是指Store表中的ID。

<小时/>

我正在尝试在单个查询的单行中获取给定商店的总合并销售额和单独销售额。我编写了如下 SQL 查询:

SELECT 
Store.ID AS 'Store ID',
SUM(IF(YEAR(main.SalesDate) = 2012 AND QUARTER(main.SalesDate) = 1,main.SalesAmount,0)) AS 'Individual Sales',
SUM(IF(YEAR(main.SalesDate) = 2012 AND QUARTER(main.SalesDate) = 1 AND YEAR(secondaries.SalesDate) = 2012 AND QUARTER(secondaries.SalesDate) = 1,main.SalesAmount + secondaries.SalesAmount,0) AS 'Consolidated Sales'
FROM Store 
LEFT JOIN Sales AS 'main' ON Store.ID = main.StoreID 
LEFT JOIN Sales AS 'secondaries' ON Store.ParentID = secondaries.StoreID 
GROUP BY Store.ID

我不明白为什么这不能按预期工作。我缺少什么？我的逻辑有什么问题吗？

Answer 1

问题在于您的查询正在生成笛卡尔积(类似)结果集。

基本上，对于“辅助”中的每个匹配行，“主”中的一行会重复多次。

要获得所需的结果，只需加入销售表一次，并匹配 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

<小时/>

更新

我的错。 (卫生部!)ParentID位于 Store表，而不是 Sales表。

上面的查询没有返回指定的结果。 (正在努力。)

我认为您已经有了解决方案......

<罢工>

<罢工>

使用ParentID列代替 ID来自Store的专栏 table 。如果 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

这不是最有效的，内联 View (或“派生表”)与 Sales 表的大小相当。将日期谓词下推到内联 View 中，或者在派生表中按月或季度进行汇总会更有效。

我更有可能将“四分之一”作为结果集的一部分返回，以允许我提取超过四分之一的内容。

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