Mysql左连接不返回连接表中的行 - mysql left join doesnt returns rows from joined table

mysql left join doesnt returns rows from joined table

本文关键字：连接返回 Mysql | 更新日期: 2023-09-27

我有两个相同的表(_impressionsdaily， _impressionsalltime)，在每天结束时，我将所有数据从daily移动到alltime。结构为:ID、purchaseID、印象、惟一、日期。

我运行如下查询:

SELECT  
    daily.purchaseID,
    SUM(daily.impression + IFNULL(alltime.impression, 0)) AS impression,
    SUM(daily.unique + IFNULL(alltime.unique, 0)) AS `unique`
    FROM _impressionsdaily daily
    LEFT JOIN _impressionsalltime alltime ON alltime.purchaseID= daily.purchaseID
    GROUP BY daily.purchaseID;

问题是，如果所有的时间表有购买，没有在每日我不会看到他们。

基本上，这个想法是总结每天和所有时间的所有印象。

——编辑事情是在每天凌晨12点，每日表被复制到所有时间，然后删除。所以给出的解决方案是错误的…

谢谢你的帮助，丹尼

我想你是在找一个联合选择。试试这样做:

SELECT DISTINCT(p.ID), p.purchaseID
FROM (SELECT *
      FROM _impressionsdaily
      UNION SELECT *
      FROM _impressionsalltime) AS p
GROUP BY p.purchaseID

SELECT  
daily.purchaseID,
SUM(daily.impression + IFNULL(alltime.impression, 0)) AS impression,
SUM(daily.unique + IFNULL(alltime.unique, 0)) AS `unique`
FROM _impressionsdaily daily
LEFT JOIN _impressionsalltime alltime ON alltime.purchaseID= daily.purchaseID
GROUP BY daily.purchaseID;

在本例中，您没有选择任何字段form _impressionsalltime table

您也可以使用这个sql从_impressionsalltime表中选择字段

SELECT  
alltime.*,
daily.purchaseID,
SUM(daily.impression + IFNULL(alltime.impression, 0)) AS impression,
SUM(daily.unique + IFNULL(alltime.unique, 0)) AS `unique`
FROM _impressionsdaily daily
LEFT JOIN _impressionsalltime alltime ON alltime.purchaseID= daily.purchaseID
GROUP BY daily.purchaseID;