使用 UNION 时 MySQL Count(*) 问题

Question

当存在多个 WHERE 子句时，我目前在 UNION 语句中使用 Count(*) 时遇到问题。

这是我正在尝试使用的 MySQL 语法:

    SELECT Count(*)
    FROM   (SELECT title,
                   taskstatus,
                   Extract(year FROM finished)  AS year,
                   Extract(month FROM finished) AS month
            FROM   todo_languages
            UNION ALL
            SELECT title,
                   taskstatus,
                   Extract(year FROM finished)  AS year,
                   Extract(month FROM finished) AS month
            FROM   todo_main
            UNION ALL
            SELECT title,
                   taskstatus,
                   Extract(year FROM finished)  AS year,
                   Extract(month FROM finished) AS month
            FROM   todo_addons) AS a
    WHERE  taskstatus = 1
           AND year = 2014
           AND month = 04 

在此处寻求帮助之前我自己尝试过什么？

首先，我测试了使用任务状态获取计数是否像这样工作:

    SELECT Count(*)
    FROM   (SELECT title,
                   taskstatus,
                   Extract(year FROM finished)  AS year,
                   Extract(month FROM finished) AS month
            FROM   todo_languages
            UNION ALL
            SELECT title,
                   taskstatus,
                   Extract(year FROM finished)  AS year,
                   Extract(month FROM finished) AS month
            FROM   todo_main
            UNION ALL
            SELECT title,
                   taskstatus,
                   Extract(year FROM finished)  AS year,
                   Extract(month FROM finished) AS month
            FROM   todo_addons) AS a
    WHERE  taskstatus = 1 

上面的代码运行良好，并返回了当前计数 1。接下来，我进行了测试，以确保按月份和年份获取计数的效果如下:

SELECT Count(*)
FROM   (SELECT title,
               taskstatus,
               Extract(year FROM finished)  AS year,
               Extract(month FROM finished) AS month
        FROM   todo_languages
        UNION ALL
        SELECT title,
               taskstatus,
               Extract(year FROM finished)  AS year,
               Extract(month FROM finished) AS month
        FROM   todo_main
        UNION ALL
        SELECT title,
               taskstatus,
               Extract(year FROM finished)  AS year,
               Extract(month FROM finished) AS month
        FROM   todo_addons) AS a
WHERE  year = 2014
       AND month = 04 

这也有效并返回行计数 1，但第一个查询返回行计数 0，这导致我来到这里。有什么我没有看到的事情是我做错了吗？提前致谢。

Answer 1

你哪里困惑了？您可以有记录 WHERE taskstatus = 1 也可以有记录 WHERE Year = 2014 AND Month = 04 但没有记录 WHERE taskstatus = 1 AND Year = 2014并且月份 = 04。

想象一下:

taskstatus     year       month
1              2000       05
2              2004       04