MYSQL 查询组合两个表的数据共享第三个表的外键

Question

我正在尝试合并两个表 sales & 的数据。在一张桌子上购买。销售和销售从第三个表存储购买表共享外键。

构造查询以获取与结果表中完全相同的结果的最佳方法是什么，最好（如果可能）不使用子查询，因为我需要创建查询结果的视图，而 MYSQL 不允许创建视图使用子查询进行查询。

我尝试使用 union 选择，但没有得到预期的结果。

底层表和查询结果

Answer 1

以下查询（未经测试）应该给出您想要的结果集：

SELECT store_name AS store, MONTH(sales.date) AS month, SUM(sales.sales) AS sales, SUM(purchase.purchase) AS purchase
FROM store 
JOIN sales ON store.store_id = sales.store_id
JOIN purchase ON store.store_id = purchase.store_id
GROUP BY store.store_id,MONTH(sales.date), MONTH(purchase.date)

Answer 2

我认为没有子查询就不可能获得预期的结果，因为您需要按月分组并从两个不同的表存储，每个表中可能有很多行，并在分组之前将它们连接在一起会增加行数。更重要的是，您需要对这些结果进行完全外连接，这在 mysql 中是不支持的，您必须通过两个相反的左连接的并集来模拟它。因此，基本上使用这个表模式，您必须使用一个非常复杂的查询，并且看起来更像是一个学术问题，而不是现实生活中的示例。

( SELECT st.store_name, s.month,
          COALESCE( s.salestotal, 0 ) as salestotal, 
          COALESCE( p.purchasetotal, 0 ) as purchasetotal
FROM 
  store st
  JOIN
    ( SELECT store_id, SUM(sales) as salestotal, 
             DATE_FORMAT( date, '%b %Y' ) as month
      FROM sales
      GROUP BY store_id, month ) s
  ON st.store_id = s.store_id
  LEFT JOIN
    ( SELECT store_id, SUM(purchase) as purchasetotal, 
      DATE_FORMAT( date, '%b %Y' ) as month
      FROM purchase
      GROUP BY store_id, month ) p
  ON p.store_id = s.store_id AND p.month = s.month )
UNION
( SELECT st.store_name, p.month,
          COALESCE( s.salestotal, 0 ) as salestotal, 
          COALESCE( p.purchasetotal, 0 ) as purchasetotal
FROM
  store st
JOIN
  ( SELECT store_id, SUM(purchase) as purchasetotal, 
           DATE_FORMAT( date, '%b %Y' ) as month
    FROM purchase
    GROUP BY store_id, month ) p
ON st.store_id = p.store_id
LEFT JOIN 
  ( SELECT store_id, SUM(sales) as salestotal, 
    DATE_FORMAT( date, '%b %Y' ) as month
    FROM sales
    GROUP BY store_id, month ) s
ON
  p.store_id = s.store_id AND p.month = s.month )
ORDER BY month, store_name