MySQL:如何将多个查询组合成一个使用具有不同条件的同一张表的查询？

Question

我有几个相似的查询，只是条件子句不同。我正在尝试将所有查询合并为一个查询。

第一个查询如下所示:

第一个查询:

SELECT pages.title, 
       YEAR(page_revisions.date) AS year,
       MONTH(page_revisions.date) AS MONTH, 
       COUNT(revid) AS Cadmin_edits 
FROM page_revisions 
    INNER JOIN pages ON page_revisions.title=pages.title
WHERE userid!=0 AND admin=1 AND pages.ns=0 
GROUP BY year, MONTH 
ORDER BY page_revisions.title, year, MONTH;

+--------------+------+-------+--------------+
| title        | year | MONTH | Cadmin_edits |
+--------------+------+-------+--------------+
| Bangalore    | 2002 |    11 |            4 |
| Bangalore    | 2003 |     2 |            2 |
| Bangalore    | 2003 |     5 |            1 |
| Bangalore    | 2003 |     6 |            6 |
| Bangalore    | 2003 |     8 |            4 |
| Bangalore    | 2003 |     9 |            3 |
| Bangalore    | 2003 |    10 |            2 |
| Bart Simpson | 2002 |     9 |            4 |
| Bart Simpson | 2007 |     3 |           33 |
| Bart Simpson | 2007 |     7 |           15 |
| Bart Simpson | 2008 |    11 |           19 |
| Batman       | 2001 |    12 |            5 |

第二个查询非常相似，唯一不同的是条件 wikip_member=1 而不是 admin。

第二个查询:

SELECT pages.title, 
       YEAR(page_revisions.date) AS year, 
       MONTH(page_revisions.date) AS MONTH, 
       COUNT(revid) AS Wpart_edits 
FROM page_revisions 
    INNER JOIN pages ON page_revisions.title=pages.title
WHERE userid!=0 AND wikip_member=1 AND pages.ns=0 
GROUP BY year, MONTH
ORDER BY page_revisions.title, year, MONTH;

+------------------------------------------+------+-------+-------------+
| title                                    | year | MONTH | Wpart_edits |
+------------------------------------------+------+-------+-------------+
| 1906 Florida Keys hurricane              | 2011 |    10 |         266 |
| 1906 Florida Keys hurricane              | 2011 |    11 |         108 |
| 1906 Florida Keys hurricane              | 2012 |     2 |          66 |
| 1969 race riots of Singapore             | 2007 |     6 |         124 |
| 1969 race riots of Singapore             | 2008 |     2 |         143 |
| 1969 race riots of Singapore             | 2009 |     1 |         151 |
| 1980 Winter Olympics                     | 2003 |     8 |           3 |
| 1980 Winter Olympics                     | 2003 |    10 |           7 |
| 1980 Winter Olympics                     | 2004 |     3 |          16 |
| Babe Ruth                                | 2004 |     5 |          22 |
| Babe Ruth                                | 2004 |     7 |          21 |
| Bangalore                                | 2002 |    11 |           6 |
| Bangalore                                | 2003 |     2 |           2 |
| Bangalore                                | 2004 |     2 |           9 |

基本上我需要的输出看起来像这样:

预期输出:

+------------------------------------------+------+-------+--------+-------+
| title                                    | year | MONTH | Cadmin | Wpart |
+------------------------------------------+------+-------+--------+-------+
| 1906 Florida Keys hurricane              | 2011 |    10 |     0  |   266 |
| 1906 Florida Keys hurricane              | 2011 |    11 |     0  |   108 |
| 1906 Florida Keys hurricane              | 2012 |     2 |     0  |    66 |
| 1969 race riots of Singapore             | 2007 |     6 |     0  |   124 |
| 1969 race riots of Singapore             | 2008 |     2 |     0  |   143 |
| 1969 race riots of Singapore             | 2009 |     1 |     0  |   151 |
| 1980 Winter Olympics                     | 2003 |     8 |     0  |     3 |
| 1980 Winter Olympics                     | 2003 |    10 |     0  |     7 |
| 1980 Winter Olympics                     | 2004 |     3 |     0  |    16 |
| Babe Ruth                                | 2004 |     4 |     0  |     7 |
| Babe Ruth                                | 2004 |     8 |     0  |    21 |
| Bangalore                                | 2002 |    11 |     4  |     6 |
| Bangalore                                | 2003 |     2 |     2  |     2 |
| Bangalore                                | 2003 |     5 |     1  |     0 |
| Bangalore                                | 2003 |     6 |     6  |     0 |
| Bangalore                                | 2003 |     8 |     4  |     0 |
| Bangalore                                | 2003 |     9 |     3  |     0 |
| Bangalore                                | 2003 |    10 |     2  |     0 |
| Bangalore                                | 2004 |     2 |     0  |     9 |

这将是查询的组合。基本上获取每个月的修订计数，并在未找到该月符合条件的修订时显示 0。我尝试了几种方法，例如 UNION、SUBSELECT，但都没有成功。 UNION 似乎没有按预期工作，而 SUBSELECT 只为 128 行运行了 5 个小时。

联合尝试:

SELECT pages.title, 
       YEAR(page_revisions.date) AS year,
       MONTH(page_revisions.date) AS MONTH, 
       COUNT(revid) AS Cadmin, 
       null AS Wpart 
FROM page_revisions 
    INNER JOIN pages ON page_revisions.title=pages.title 
WHERE userid!=0 AND admin=1 AND pages.ns=0 
UNION 
SELECT pages.title, 
       mid( page_revisions.date, 1, 4) AS year, 
       mid(page_revisions.date, 6, 2) AS MONTH, 
       null as Cadmin,
       COUNT(revid) AS Wpart_edits 
FROM page_revisions 
    INNER JOIN pages ON page_revisions.title=pages.title 
WHERE userid!=0 AND wikip_member=1 AND pages.ns=0 
GROUP BY year, MONTH 
ORDER BY title, year, MONTH;

子选择尝试:

SELECT pages.title, 
       YEAR(page_revisions.date) AS year,
       MONTH(page_revisions.date) AS MONTH, 
       (SELECT COUNT(revid) AS Cadmin_edits FROM page_revisions WHERE YEAR(page_revisions.date)=year and MONTH(page_revisions.date)= month and userid!=0 AND admin=1) 
FROM page_revisions 
    INNER JOIN pages ON page_revisions.title=pages.title
WHERE pages.ns=0 
GROUP BY year, MONTH 
ORDER BY pages.title, year, MONTH;

如能为我指明正确的方向，我们将不胜感激。这是我将于周五提交的最终演示文稿，这是我需要将所有内容打包在一起的最后一个查询。

Answer 1

在 MySql(和许多其他 SQL 方言)中，您可以连接到子查询。这应该执行得更好，因为引擎将运行一次查询以生成单个结果集，而不是每行初始结果运行一次作为 select 子句一部分的子查询。

所以，你会尝试这样的事情:

select COALESCE(a.title, b.title), 
   COALESCE(a.year, b.year), 
   COALESCE(a.month, b.month),
   COALESCE(a.Cadmin_edits, 0),
   COALESCE(b.Wpart_edits, 0)
FROM (SELECT pages.title, 
      YEAR(page_revisions.date) AS year,
      MONTH(page_revisions.date) AS MONTH, 
      COUNT(revid) AS Cadmin_edits 
   FROM page_revisions 
   INNER JOIN pages ON page_revisions.title=pages.title
   WHERE userid!=0 
      AND admin=1 
      AND pages.ns=0 
   GROUP BY year, MONTH 
   ORDER BY page_revisions.title, year, MONTH) a
FULL JOIN (SELECT pages.title, 
      YEAR(page_revisions.date) AS year, 
      MONTH(page_revisions.date) AS MONTH, 
      COUNT(revid) AS Wpart_edits 
   FROM page_revisions 
   INNER JOIN pages ON page_revisions.title=pages.title 
   WHERE userid!=0 
      AND wikip_member=1 
      AND pages.ns=0 
   GROUP BY year, MONTH 
   ORDER BY page_revisions.title, year, MONTH) b 
ON a.title = b.title AND a.year = b.year AND a.month = b.month
/*order the full results as you please*/

如果这是 Oracle 或 Sql Server，我会为子查询推荐几个通用表表达式，因为它们允许您从主查询中删除子查询，使完整查询更容易理解。