MySQL LEFT JOIN 与 GROUP BY 和 WHERE IN（子查询）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15321182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL LEFT JOIN with GROUP BY and WHERE IN (sub query)
提问by sylling
I have one table with some statistics per date, which I want listed out with MySQL. For some dates there will be no statistics, so the result should look something like this:
2013-03-01: 3
2013-03-02: 2
2013-03-03: 0
2013-03-04: 1
我有一张表，每个日期都有一些统计信息，我想用 MySQL 列出。对于某些日期将没有统计数据，因此结果应如下所示：
2013-03-01: 3
2013-03-02: 2
2013-03-03: 0
2013-03-04: 1
I figured out that filling in the gaps with 0 -zero- could be solved with a separate table with all possible dates and LEFT JOIN. So far so good.
我发现用 0 -zero- 填充空白可以用一个单独的表来解决，其中包含所有可能的日期和 LEFT JOIN。到现在为止还挺好。
The statistics (impressions) is in the table 'campaigndata':
统计数据（展示次数）在“campaigndata”表中：
id - int(11) date - date campaignid - int(11) impressions - int(11)
But I want to get only some of the statistics. To be more specific, I only want the rows from 'campaigndata' where 'campaignid' is in the table 'campaignfilter' with 'campaigntype' set to 1 (as an example).
但我只想得到一些统计数据。更具体地说，我只想要来自 'campaigndata' 的行，其中 'campaignid' 在表 'campaignfilter' 中，'campaigntype' 设置为 1（作为示例）。
This is the table 'campaignfilter':
这是“campaignfilter”表：
id - int(11) campaigntype - int(11) campaignid - int(11)
Anyone have clue how this could be done?
任何人都知道如何做到这一点？
PS: The structure of table 'campaigndata' is pretty much locked, since it is based on an automatic import from an external system.
PS：表“campaigndata”的结构几乎是锁定的，因为它是基于从外部系统自动导入的。
SAMPLE RECORDS
样本记录
CREATE TABLE demo_campaigndata (
id int(11) NOT NULL AUTO_INCREMENT,
date date NOT NULL,
campaignid int(11) NOT NULL,
impressions int(11) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO demo_campaigndata (id, date, campaignid, impressions) VALUES
(1, '2013-03-03', 1, 100),
(2, '2013-03-03', 2, 100),
(3, '2013-03-03', 3, 100),
(4, '2013-03-04', 2, 100),
(5, '2013-03-05', 1, 100),
(6, '2013-03-05', 2, 100);
CREATE TABLE demo_campaignfilter (
id int(11) NOT NULL AUTO_INCREMENT,
campaigntype int(11) NOT NULL,
campaignid int(11) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO demo_campaignfilter (id, campaigntype, campaignid) VALUES
(1, 1, 1),
(2, 1, 3);
CREATE TABLE demo_calendar (
date date NOT NULL,
PRIMARY KEY (date)
);
INSERT INTO demo_calendar (date) VALUES
('2013-03-01'),
('2013-03-02'),
('2013-03-03'),
('2013-03-04'),
('2013-03-05');
DESIRED RESULT
想要的结果
2013-03-01: 0
2013-03-02: 0
2013-03-03: 200
2013-03-04: 0
2013-03-05: 100
回答by John Woo
SELECT a.date, COUNT(b.campaignid) totalStat
FROM campaigndata a
LEFT JOIN campaignfilter b
ON a.campaignid = b.campaignid AND
b.campaigntype = 1
GROUP BY a.date
To further gain more knowledge about joins, kindly visit the link below:
要进一步了解有关联接的更多信息，请访问以下链接：
UPDATE 1
更新 1
SELECT a.date,
COALESCE(b.totals,0) totals
FROM demo_calendar a
LEFT JOIN
(
SELECT a.date, SUM(impressions) totals
FROM demo_campaigndata a
INNER JOIN demo_campaignfilter b
ON a.campaignid = b.campaignid
WHERE b.campaigntype = 1
GROUP BY a.date
) b ON a.date = b.date
