MySQL右连接与分组问题

Question

我有两个表：

1. LandingPages - 包含每个广告系列的落地页。

2. Reports - 包含每个落地页的点击量和转化量。

我试图查询每个落地页的点击量和转化量之和，但是如果某个落地页没有接收到任何点击量和转化量（在报告表中也没有显示），那么我希望返回结果为0。 到目前为止，我的做法是：

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM Report c
RIGHT JOIN LandingPages l ON(c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x  
AND DayDate > 'y'
GROUP BY c.LandingPageId

问题在于我只能得到存在于报告表并且日期为“y”的着陆页的行（例如：我只能得到2个着陆页的行，但实际上有4个着陆页）。如果我运行此查询，则会返回4个结果。

SELECT l.LandingPageId FROM LandingPages l WHERE l.CampaignId = x 

不是所有着陆页面都有0的价值，

我该如何让它按照我想要的方式工作，并给我那些不在报告表中或在旧日期表中但仍然是着陆页的页面？

谢谢。

更新：

非常感谢您的回答，我得到了部分解决方案，只有当着陆页面在报告表中不存在时才适用于我，但是如果它存在但日期条件不匹配，则不会出现：

部分解决方案查询：

 SELECT l.LandingPageId, IFNULL(SUM(Hits),0) AS Hits, IFNULL(SUM(PixelSum),0)  AS Conversion
    FROM LandingPages l
    LEFT JOIN Report c  ON( l.LandingPageId = c.LandingPageId) 
    WHERE (l.CampaignId = x OR  l.CampaignId IS NULL) 
    AND (DayDate > 'y' OR DayDate IS NULL)
    GROUP BY l.LandingPageId

我仍然需要您的帮助！

谢谢！

- Haim Evgi

---

你能展示某个活动的两个表格内容吗？如果不进行 GROUP BY，你会得到多少行数据？你应该得到 4 行。记得要进行求和操作。 - hol

报告表格每个活动包含数千行，因此我不会带来这个。 - Haim Evgi

如果Jürgen Hollfelder的表格创建正确，您能否打印出您期望结果表格提供的内容？谢谢。 - Frankie

Answer 1

好的。当我运行以下代码时，会得到下面的结果。这是否是您想要的？

drop table landingpages;
create table landingpages (campaignid number, landingpageid number,  daydate number);

insert into landingpages values (1,100,20);
insert into landingpages values (1,101,21);
insert into landingpages values (2,102,20);
insert into landingpages values (2,103,21);

drop table report;
create table report (campaignid number, landingpageid number, hits number, pixelsum number);

insert into report values (1,100, 2, 1 );
insert into report values (2,102, 20, 21 );
insert into report values (2,103, 30, 31 );

commit;

SELECT c.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion  
    FROM landingpages c 
    LEFT JOIN report l ON(c.LandingPageId = l.LandingPageId ) 
    WHERE c.CampaignId = 1   
    AND DayDate > 19 
    GROUP BY c.LandingPageId 


LANDINGPAGEID       HITS CONVERSION
------------- ---------- ----------
          100          2          1
          101                      


2 rows selected.

我希望这正是您所需要的。我在Oracle上运行了上述内容，但在mySQL上应该没有什么实质性的区别，因为这都是标准查询语言。

- hol

---

@haim evgi - 他接近了，你需要首先从 LandingPages 表中选择，因为你想要该表中的所有记录，然后左连接报告以获取来自该表的匹配记录。 - JNK

你能否添加一个参考链接，例如http://www.w3schools.com/sql/sql_join.asp，以便简要解释连接类型？这并不值得一篇完整的回答 :) - extraneon

同意。链接会很好。我想添加一个。我只有德国网站的链接。所以感谢您的帮助。 - hol

+1 我很感激你所做的事情，但在 MySQL 中它对我不起作用，我不知道为什么，这很奇怪。 - Haim Evgi

Answer 2

首先，你没有告诉我们 Hits、PixelSum 或者 DayDate 存储在哪个表中。在我的查询中，? 代表这一事实。显然，? 需要用正确的别名替换。但是，鉴于你后来提到如果日期标准不匹配会有问题，我假设 DayDate 来自 Report 表。 简而言之，在左连接的 ON 子句中应用该标准。ON 子句标准在加入 LandingPages 表之前被应用。因此，在 Report 表与 LandingPages 表连接之前，将过滤掉除 DayDate 值 <= 'y'（顺便问一下，DayDate > 'y' 的数据类型是什么？看起来可疑）以外的 Campaigns <> X。 此外，你应该考虑使用 Coalesce 而不是 IsNull，因为 Coalesce 是 ISO 标准。

Select L.LandingPageId
    , Coalesce( Sum( ?.Hits ), 0 ) As Hits
    , Coalesce( Sum( ?.PixelSum ), 0 ) As Conversion
From LandingPages As L
    Left Join Report As R
        On R.LandingPageId = L.LandingPageId
            And R.CampaignId = X
            And ( R.DayDate > 'y' Or R.DayDate Is Null )
Group By L.LandingPageId

关于左连接的更多信息，请参考这个可视化解释。

- Thomas

Answer 3

这是因为使用了RIGHT JOIN。请重新运行以下代码：

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM LandingPages l
LEFT JOIN Reports c ON(c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x  
AND DayDate > 'y'
GROUP BY c.LandingPageId

- JNK

---

我只得到了2个着陆页面的行数，但实际上应该有4个着陆页面。 - Haim Evgi

如果您注释掉 JOIN 并运行，是否会得到所有4条记录？请注意，我不仅改变了表的顺序，还将其更改为 LEFT JOIN。 - JNK

如何注释掉 join？如果我执行以下语句：SELECT l.LandingPageId FROM LandingPages l WHERE l.CampaignId = x，我会得到4个结果。 - Haim Evgi

由于你在WHERE子句中使用了日期，因此你需要同时添加日期。另外，你确定你的分组操作不会减少结果集吗？ - JNK

你的意思是什么？日期在报表中，我需要它只显示通过这个日期的记录。 - Haim Evgi

抱歉，您没有包含模式！请阅读其他答案的评论中有关JOIN的页面。这应该很容易做到。 - JNK

Answer 4

你有这个代码：WHERE c.CampaignId = x 这意味着如果落地页没有收到任何点击和转化（并且不在报告表中显示），那么这个落地页将永远不会出现在结果中，即使你使用了正确的连接。对于那些落地页，你的c.CampaignId将为null，而c.CampaignId = x将为false。

尝试一下：

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM Report c
RIGHT JOIN LandingPages l ON(c.LandingPageId = l.LandingPageId )
WHERE (c.CampaignId = x  or c.CampaignId is null)  
AND DayDate > 'y'
GROUP BY l.LandingPageId

我也按 l.LandingPageId 进行分组，因为对于没有报告的落地页，c.LandingPageId 为 null。

- ceteras

Answer 5

我借鉴了Thomas的想法，并进行了一些改进！ 查询：

Select L.LandingPageId
    , Coalesce( Sum( R.Hits ), 0 ) As Hits
    , Coalesce( Sum( R.PixelSum ), 0 ) As Conversion
From LandingPages As L
    Left Join Report As R
        On R.LandingPageId = L.LandingPageId
            And L.CampaignId = X
            And R.DayDate > 'y' 
WHERE L.CampaignId = X
Group By L.LandingPageId

- Haim Evgi

Answer 6

我看到一些问题...

• 请在所有情况下都加上前缀（即l.fieldname，c.fieldname），以便我可以知道您从哪个表中获取它们。无论如何，我为您编写了一些示例代码，但由于我并不总是知道表格，这对于RIGHT JOIN很重要，因此您可能需要进行调整。
• 当您在右连接的表上设置条件（WHERE c.CampaignID = something）时，您将其转换为INNER JOIN。如果您想避免这种情况，请添加“...或c.CampaignID为null）。因为RIGHT join的想法是，如果有一个campaignID，您希望它是'x'，但是如果没有活动，那也没关系。（对吗？）

您不能对空值求和，因此我添加了coalesce以将null更改为零。

SELECT 
   l.LandingPageId, 
   SUM(COALESCE(Hits,0)) AS Hits, 
   SUM(PixelSum) AS Conversion 
FROM 
      Report c
   RIGHT JOIN 
      LandingPages l 
   ON
      (c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x OR c.CampaignID is null 
AND DayDate > 'y'
GROUP BY c.LandingPageId

- dave