Mysql查询联合所有

Question

我在 MySQL 数据库中执行了两个查询。

两个查询之间的唯一区别是附加条件:

WHERE
    Action > ''

两个查询:

mysql> SELECT
 COUNT(DISTINCT tel_number) AS `cases present`
FROM
    `tbl_tel_number`
WHERE
    Consuming IS NOT NULL
GROUP BY
    Zone,
    Consuming
LIMIT 3;

SELECT
 COUNT(DISTINCT tel_number) AS `cases analyzed`
FROM
    `tbl_tel_number`
WHERE
    Action > ''
GROUP BY
    Zone,
    Consuming
LIMIT 3;
+---------------+
| cases present |
+---------------+
|             7 |
|            27 |
|            27 |
+---------------+
3 rows in set

+-----------------+
| cases analyzed  |
+-----------------+
|               1 |
|               3 |
|               3 |
+-----------------+
3 rows in set

现在我尝试联合两个查询的结果集并测试这个解决方案:

mysql> SELECT
 COUNT(DISTINCT tel_number) AS `cases present`,
 CASE
WHEN Action > '' THEN
    COUNT(DISTINCT tel_number)
ELSE
    0
END AS `cases analyzed`
FROM
    `tbl_tel_number`
WHERE
    Consuming IS NOT NULL
GROUP BY
    Zone,
    Consuming
LIMIT 3;


+---------------+-----------------+
| cases present | cases analyzed  |
+---------------+-----------------+
|             7 |               0 |
|            27 |               0 |
|            27 |              27 |
+---------------+-----------------+
3 rows in set

mysql> 

但是输出是错误的，因为在对已分析案例的第二次查询中，我:

+-----------------+
| cases analyzed  |
+-----------------+
|               1 |
|               3 |
|               3 |
+-----------------+
3 rows in set

而不是联合查询我分析过的案例，为什么？

+-----------------+
| cases analyzed  |
+-----------------+
|               0 |
|               0 |
|              27 |
+-----------------+
3 rows in set

Answer 1

您想将条件放在计数中:

SELECT COUNT(DISTINCT tel_number) AS `cases present`,
       COUNT(DISTINCT CASE WHEN Action > '' THEN tel_number END) as `cases analyzed`
FROM `tbl_tel_number`
WHERE Consuming IS NOT NULL
GROUP BY Zone, Consuming
LIMIT 3;