代码之家 › 专栏 › 技术社区 › Haim Evgi

mysql按问题分组右连接

group-by join mysql

Haim Evgi · 技术社区 · 14 年前

登陆页面 -包含每个活动的登录页。
报告 -包含每个登录页的点击率和转化率。

并且不显示在报表表中 )然后我希望返回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

问题是,我只得到带有登录页的行,这些行存在于reports表中,并传递日期“y”,

如果我运行这个查询,我会得到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

谢谢!

6 回复 | 直到 14 年前

hol 14 年前

可以。当我运行以下命令时,我从下面得到结果。这就是你想要的吗?

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.

Thomas 14 年前

首先,你没有告诉我们在哪张桌子上 Hits , PixelSum ,或 DayDate 被储存。这个 ? ? 将需要替换为正确的别名。然而,我认为来自报告表,因为您后来提到了一个问题,如果日期标准不匹配。

简而言之,您需要在左Join的ON子句中应用该条件。采用ON子句标准之前它被联接到LandingPages表。因此,活动<&燃气轮机;除了 日期 值<='顺便问一下,你的数据类型是什么 DayDate > 'y' 在报表表连接到LandingPages表之前。

另外,你应该考虑使用 Coalesce IsNull 自从是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

For more information on Left Joins, here is a visual representation

JNK 14 年前

这是右连接的b/c。重新运行为:

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

ceteras 14 年前

你有这个: WHERE c.CampaignId = x 这意味着,如果登录页没有收到任何点击和转换(并且没有在报告表中显示),则登录页将永远不会显示在结果中,尽管您使用了right join。对于这些登录页,您的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。

Haim Evgi 14 年前

我接受托马斯的想法, 而且几乎没有改进它的工作!

查询:

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

dave 14 年前

请在所有情况下为您的列(即,l.fieldname,c.fieldname)添加前缀,这样我就可以知道您从哪个表获取它们。不管怎样,我在下面为您制作了一些示例代码,但是我不能100%地确定它,因为我并不总是知道表,这对于正确的连接很重要,所以您可能需要调整它。
在右联接表上设置条件(其中c.CampaignID=something)时,将其转换为内部联接。如果要避免这种情况,请添加“…或c.CampaignID为null)。因为正确连接的想法是, 如果有一个campaignID,你希望它是'x',但是如果没有campaignID,那也没关系(是吗?)

你不能对空值求和,所以我添加了coalesce将空值改为零。

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