我有两张桌子
mysql> select * from report;
+----+----------+------------+------------------+-------------+
| id | campaign | advertiser | impression_count | click_count |
+----+----------+------------+------------------+-------------+
| 1 | camp1 | adv1 | 20 | 6 |
| 2 | camp2 | adv2 | 10 | 2 |
| 3 | camp1 | adv1 | 15 | 3 |
| 4 | camp2 | adv2 | 6 | 1 |
+----+----------+------------+------------------+-------------+
4 rows in set (0.00 sec)
mysql> select * from device;
+-----------+-----------+
| report_id | device_id |
+-----------+-----------+
| 1 | d1 |
| 1 | d2 |
| 2 | d1 |
| 2 | d3 |
| 2 | d4 |
| 3 | d2 |
| 3 | d4 |
| 4 | d3 |
| 4 | d4 |
| 4 | d5 |
+-----------+-----------+
10 rows in set (0.00 sec)
我想要的报告，是在活动和广告商的水平，有印象和点击计数和不同的设备ID的总和汇总。所以我写了下面的问题
SELECT
campaign,
advertiser,
sum(impression_count),
sum(click_count),
count(DISTINCT device_id)
FROM report
LEFT JOIN device ON report.id = device.report_id
GROUP BY campaign, advertiser;
+----------+------------+-----------------------+------------------+---------------------------+
| campaign | advertiser | sum(impression_count) | sum(click_count) | count(distinct device_id) |
+----------+------------+-----------------------+------------------+---------------------------+
| camp1 | adv1 | 70 | 18 | 3 |
| camp2 | adv2 | 48 | 9 | 4 |
+----------+------------+-----------------------+------------------+---------------------------+
这里由于连接印象计数和单击计数被聚合为多行。想要的是
+----------+------------+-----------------------+------------------+---------------------------+
| campaign | advertiser | sum(impression_count) | sum(click_count) | count(distinct device_id) |
+----------+------------+-----------------------+------------------+---------------------------+
| camp1 | adv1 | 35 | 9 | 3 |
| camp2 | adv2 | 16 | 3 | 4 |
+----------+------------+-----------------------+------------------+---------------------------+
http://sqlfiddle.com/#!2/05dd9d/1
找到了不太好的解决方案
select campaign,advertiser,ic,cc,count(distinct device_id)
from (
select
group_concat(id) as id,
sum(impression_count)as ic,
sum(click_count)as cc,
campaign,advertiser
FROM report har GROUP BY campaign,advertiser) a
LEFT JOIN device dr ON FIND_IN_SET(dr.report_id, a.id)
group by a.id
);
但这使用群concat，所以如果群concat结果的长度较大可能会有问题。
最佳答案
您要做的是执行两个不同的查询，然后连接结果集。外部选择只是选择我们实际需要的信息，并在一个公共值上连接两个临时表。如果不想为整个活动选择设备表中的不同设备，也可以使用id和report-id来完成此操作。
select `firsttable`.campaign, `firsttable`.advertiser, a, b, c from
(select id, campaign, advertiser, sum(impression_count) as a, sum(click_count) as b
from report
group by campaign, advertiser
) as firsttable
left join
(select campaign, advertiser, count(distinct device_id) as c
from device, report
where id=report_id
group by campaign, advertiser
) as secondtable on `firsttable`.campaign=`secondtable`.campaign and
`firsttable`.advertiser=`secondtable`.advertiser;
SqlFiddle:http://sqlfiddle.com/#!2/8bd63/20
此查询是这两个临时表的组合：
| ID | CAMPAIGN | ADVERTISER | A | B |
|----|----------|------------|-----|-----|
| 1 | camp1 | adv1 | 35 | 9 |
| 5 | camp1 | adv2 | 900 | 900 |
| 2 | camp2 | adv2 | 16 | 3 |
| CAMPAIGN | ADVERTISER | C |
|----------|------------|---|
| camp1 | adv1 | 3 |
| camp2 | adv2 | 4 |
结果：
| CAMPAIGN | ADVERTISER | A | B | C |
|----------|------------|-----|-----|--------|
| camp1 | adv1 | 35 | 9 | 3 |
| camp1 | adv2 | 900 | 900 | (null) |
| camp2 | adv2 | 16 | 3 | 4 |
查询的问题是，当将报表表与设备表组合时，它会复制行。你会得到这样的结果：
| CAMPAIGN | ADVERTISER | IMPRESSION_COUNT | CLICK_COUNT | DEVICE_ID |
|----------|------------|------------------|-------------|-----------|
| camp1 | adv1 | 20 | 6 | d1 |
| camp1 | adv1 | 20 | 6 | d2 |
| camp2 | adv2 | 10 | 2 | d1 |
| camp2 | adv2 | 10 | 2 | d3 |
| camp2 | adv2 | 10 | 2 | d4 |
| camp1 | adv1 | 15 | 3 | d2 |
| camp1 | adv1 | 15 | 3 | d4 |
| camp2 | adv2 | 6 | 1 | d3 |
| camp2 | adv2 | 6 | 1 | d4 |
| camp2 | adv2 | 6 | 1 | d5 |
| camp1 | adv2 | 900 | 900 | (null) |
关于mysql - MySQL查询:聚合处于两个不同的级别，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27086813/