sql - 从 2 个表中获取结果计数

Question

我从早上开始就努力做以下事情来获得解决方案。我有 2 张 table

表1和表2

表1

Impressions     Clicks       RetailerId  ManufacturerId      Date
230             1273           5          104             2013-10-23 08:46:21.377
240             1220           12         104             2013-10-23 08:46:21.377
340             1530           8          102             2013-10-23 08:46:21.377
220             2012           25         102             2013-10-23 08:46:21.377

表2

Earnings        CreatedAt                   RetailerId      ManufacturerId
20.0            2013-10-23 08:46:21.3775       25               104
21.0            2013-10-23 08:46:21.37712      12               104
15.5            2013-10-23 08:46:21.3778       12               102
16.2            2013-10-23 08:46:21.377        25               102

我需要连接两个表，并且两个表没有任何外键关系，因为表 1 数据已汇总然后更新。

我的目的是需要获取特定制造商在选定日期之间的所有零售商的总点击数、总印象数、总收入。

当我执行一些聚合函数时，我没有得到预期的结果。

到目前为止我所做的是

select rs.retailerid, rs.Clicks,asr.TotalValue, rs.ManufacturerId, asr.ManufacturerId
from(select rs.RetailerId,rs.ManufacturerId,SUM(rs.WidgetClicks) as Clicks
from RetailerStats rs group by RetailerId,ManufacturerId)rs
join
(select asr.retailerid,asr.ManufacturerId,SUM(asr.Earnings) as TotalValue
from AffiliateSchemeReports asr group by RetailerId,ManufacturerId)asr
on rs.RetailerId = asr.RetailerId
where rs.ManufacturerId = 104 and asr.ManufacturerId = 104

当我在聚合函数中给出日期部分时，它没有给我实际值。

非常感谢任何帮助!

Answer 1

一种想法是使用完全外连接来获取值，即使它们只在一个表中。我还将制造商 ID 移至 join 条件:

select coalesce(rs.retailerid, asr.retailerid) as retailerid,
       coalesce(rs.Clicks, 0) as clicks,
       coalesce(asr.TotalValue, 0) as TotalValue,
       rs.ManufacturerId, asr.ManufacturerId
from (select rs.RetailerId, rs.ManufacturerId, SUM(rs.WidgetClicks) as Clicks
      from RetailerStats rs
      group by RetailerId, ManufacturerId
     ) rs full outer join
     (select asr.retailerid, asr.ManufacturerId, SUM(asr.Earnings) as TotalValue
      from AffiliateSchemeReports asr
      group by RetailerId, ManufacturerId
     ) asr
     on rs.RetailerId = asr.RetailerId and
        rs.ManufacturerId = asr.ManufacturerId
where (rs.ManufacturerId = 104 or asr.ManufacturerId = 104)