mysql - 在 SQL 中合并两个不同查询的结果集

Question

我有 2 个包含饮料数据的表。一张表有关于售出多少饮料的信息，第二张表有新饮料的数据。

我想获取有关日期的数据，希望如下所示。这两个表都有两个共同的“日期”和“饮料”列。
每天都会出售饮料并添加新的饮料。我正在使用交叉连接来组合这两个表中的数据，复杂的部分是如果任何表在特定日期销售或带来的饮料数量为 0，我想显示 0。
任何人都可以提供任何解决方案，以解决如何以下列样式组合数据。任何帮助将不胜感激。

select *  from 
(  
   select Date(sellingDate) as 'Date', softDrink as 'Drinks',  Count(*)  as 'Sold'
   from sell where Date(sellingDate)= '1-1-2020' 
   group by Date(sellingDate), softDrink
) A

cross join 
(
   select Date(u.createdDate) as 'Date',softDrink as 'Drinks',  Count(*)  as 'Brought'
   from new_drinks as n
   inner join category as c on n.erID = c.erID
   where Date(c.createdDate)='1-1-2020' 
   group by Date(c.CreatedDate), n.softDrink
) B 

标签： mysqlsql

Answer 1

@Andrian-无论如何，您的查询都应该给您错误，因为在您的第二个查询中没有别名为“u”的表。此外，我们需要一个联合和聚合函数来获得您想要的输出。

SELECT Date, Drinks, SUM(Sold) AS Sold, SUM(Brought) AS Brought
FROM
(SELECT Date(sellingDate) AS 'Date', softDrink AS 'Drinks',  Count(*)  AS 'Sold', 0 AS 'Brought'
FROM sell s
WHERE Date(s.sellingDate)= '1-1-2020' 
GROUP BY  Date(sellingDate), softDrink
UNION 
SELECT Date(c.createdDate) AS 'Date',softDrink AS 'Drinks', 0 AS 'Sold' , Count(*)  AS 'Brought'
FROM new_drinks AS n
INNER JOIN category AS c on n.erID = c.erID
WHERE Date(c.createdDate)='1-1-2020' 
GROUP BY Date(c.CreatedDate), n.softDrink
) A
GROUP BY Date, Drinks