MySQL LEFT JOIN 与 GROUP BY 和 WHERE IN(子查询)

Question

我有一张表，每个日期都有一些统计信息，我想用 MySQL 列出。对于某些日期，将没有统计信息，因此结果应如下所示:
2013-03-01: 3
2013-03-02:2
2013-03-03: 0
2013-03-04: 1

我发现用 0 -zero- 填充空白可以通过一个包含所有可能日期和 LEFT JOIN 的单独表来解决。到目前为止一切顺利。

统计信息(展示次数)在“campaigndata”表中:

id - int(11)date - datecampaignid - 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).

This is the table '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.

---

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');

期望的结果

2013-03-01: 0
2013-03-02: 0
2013-03-03: 200
2013-03-04: 0
2013-03-05: 100

Answer 1

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

如需进一步了解联接，请访问以下链接:

• Visual Representation of SQL Joins

更新 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

• SQLFiddle Demo