mysql - 每天在 Select COUNT(*) 中合并 2 个查询

Question

我有 2 个表日志和轨道，每个表都有一个时间戳，但名称不同，现在我尝试获取给定商店每天的所有日志，同时获取我的轨道表中每天的轨道数量

SELECT DATE(clicktime), COUNT(shop)
FROM LOG
WHERE shop = "shop01"
AND (clicktime > DATE_SUB(NOW(), INTERVAL 30 DAY))
GROUP BY DATE(clicktime);

此查询每天收集日志表中的所有条目，结果如下所示

--------------------------------
ClickTime          |  Clicks
--------------------------------
2014-12-25         |   342    
-------------------------------- 
2014-12-24         |   232
--------------------------------

我在我的第二个表上得到每天的销售额，如下所示

SELECT DATE(last_change) as SaleTime, COUNT(shop) as sale
FROM tracks
WHERE shop = "dd01"
AND (last_change > DATE_SUB(NOW(), INTERVAL 30 DAY))
AND relevant = 1
GROUP BY DATE(last_change)

输出

--------------------------------
SaleTime           |  sales
--------------------------------
2014-12-25         |   42    
-------------------------------- 
2014-12-24         |   32
--------------------------------

我想将这两个查询合并为一个输出

-----------------------------------------
Time               |  sales    |  clicks
-----------------------------------------
2014-12-25         |   42      |  342
----------------------------------------- 
2014-12-24         |   32      |  232
-----------------------------------------

我正在尝试使用 Union 作为子查询，但由于不同的表结构，这个在可能的情况下无法工作

有没有办法达到预期的效果？

Answer 1

试试这个:

SELECT A.clicktime, A.shopCnt, B.saleCount
FROM (SELECT DATE(clicktime) clicktime, COUNT(shop) AS shopCnt
        FROM LOG
        WHERE shop = "shop01"
        AND (clicktime > DATE_SUB(NOW(), INTERVAL 30 DAY))
        GROUP BY DATE(clicktime)
      ) AS A 
INNER JOIN (SELECT DATE(last_change) AS SaleTime, COUNT(shop) AS saleCount
                FROM tracks
                WHERE shop = "dd01"
                AND (last_change > DATE_SUB(NOW(), INTERVAL 30 DAY))
                AND relevant = 1
                GROUP BY DATE(last_change)
              ) AS B ON A.clicktime = B.SaleTime