MySQL我可以将这两个查询合并在一起吗？

I want to get the results of these two queries together but can't figure out how to combine them. The goal is to get the total sales per month by product. I have instore purchases and online orders. Here is the first query to get the total sales per month from online orders:

我想将这两个查询的结果放在一起,但无法弄清楚如何将它们组合起来。目标是按产品获得每月的总销售额。我有店内购买和在线订单。这是第一个从在线订单获得每月总销售额的查询:

SELECT YEAR( orderDate ) AS "SalesYear"
     , MONTH( orderDate ) AS "SalesMonth"
     , SUM( orderTotal ) AS "TotalSales"
     , products.productID 
FROM orders
INNER JOIN orderdetails ON orders.orderID = orderDetails.orderID
INNER JOIN products ON orderDetails.productID = products.productID
GROUP BY productID, YEAR( orderDate ) , MONTH( orderDate )
ORDER BY YEAR( orderDate ) , MONTH( orderDate )

Here is the query that retrieves total sales per month by product from in store purchases:

以下是按商店购买的产品检索每月总销售额的查询:

SELECT YEAR( orderDate ) AS "SalesYear"
    , MONTH( orderDate ) AS "SalesMonth"
    , SUM( orderTotal ) AS "TotalSales"
    , products.productID 
FROM in_storepurchase
INNER JOIN instorepurchasedetails 
   ON in_storepurchase.isPurchaseID = instorepurchasedetails.isPurchaseID
INNER JOIN products 
   ON instorepurchasedetails.productID = products.productID
 GROUP BY productID, YEAR( orderDate ) , MONTH( orderDate )
 ORDER BY YEAR( orderDate ) , MONTH( orderDate )

Any help on how I could get this into one query so I can get all the results on a single table would be appreciated.

任何有关如何将其纳入一个查询的帮助,以便我可以在单个表上获得所有结果将不胜感激。

1 个解决方案

#1

Of course, you can use UNION:

当然,你可以使用UNION:

SELECT 
    YEAR( t.orderDate ) AS "SalesYear", MONTH( t.orderDate ) AS "SalesMonth", SUM( t.orderTotal ) AS "TotalSales", productID
FROM
(
 select 1 
     UNION ALL
 select 2 
) as t
GROUP BY
    t.productID, YEAR( t.orderDate ) , MONTH( t.orderDate )