mysql - 如何使用 WHERE 语句和 COUNT(*) = 1 选择行

Question

我正在尝试将 WHERE 子句与 HAVING COUNT(*) 子句结合起来。但是我得不到我想要的结果。

我想找到所有只下了一个订单且订单为“inactive” 的客户。换句话说，我试图从订单表中选择行，其中 customer_name 的计数为“1”且订单状态等于 “inactive”。

我有这张表:

orders_tbl
+----+--------------+-------------+---------+-------+
| ID |    cust_name |      status | item_no | price |
+----+--------------+-------------+---------+-------+
|  1 |        Scott |      active |       4 |   2.0 |
+----+--------------+-------------+---------+-------+
|  2 |        James |      active |       2 |   4.0 |
+----+--------------+-------------+---------+-------+
|  3 |         Eric |    inactive |       3 |   8.0 |
+----+--------------+-------------+---------+-------+
|  4 |        Polly |      active |       3 |   2.0 |
+----+--------------+-------------+---------+-------+
|  5 |        Peggy |    inactive |       6 |   4.0 |
+----+--------------+-------------+---------+-------+
|  6 |         Earl |    inactive |       1 |   5.0 |
+----+--------------+-------------+---------+-------+
|  7 |        Billy |      active |       4 |   2.0 |
+----+--------------+-------------+---------+-------+
|  8 |        Peggy |    inactive |       5 |   4.0 |
+----+--------------+-------------+---------+-------+
|  9 |        Jenny |    inactive |       4 |   8.0 |
+----+--------------+-------------+---------+-------+
| 10 |        Polly |      active |       2 |   2.0 |
+----+--------------+-------------+---------+-------+
| 11 |        Scott |    inactive |       2 |   4.0 |
+----+--------------+-------------+---------+-------+
| 12 |        James |    inactive |       1 |   8.0 |
+----+--------------+-------------+---------+-------+

我想要每个只有一个订单且订单“不活跃”的客户的姓名。从上表中，我想要这些结果:

+----+--------------+-------------+---------+-------+
| ID | cust_name    |     status  | item_no | price |
+----+--------------+-------------+---------+-------+
|  3 |         Eric |    inactive |       3 |   8.0 |
+----+--------------+-------------+---------+-------+
|  6 |         Earl |    inactive |       1 |   5.0 |
+----+--------------+-------------+---------+-------+
|  9 |        Jenny |    inactive |       4 |   8.0 |
+----+--------------+-------------+---------+-------+

我尝试了这个查询，以及许多使用 WHERE 和 COUNT() 的变体:

SELECT ID, cust_name, status, item_no, price, COUNT(*) 
FROM orders_tbl
WHERE status = 'inactive'
GROUP BY cust_name
HAVING COUNT(*)<2;

上述查询产生的结果接近我想要的结果。但是我得到的客户有一个 "inactive" 记录，即使他们有一个或多个事件记录。我得到的结果是:

orders_tbl
+----+--------------+-------------+---------+-------+
| ID |    cust_name |      status | item_no | price |
+----+--------------+-------------+---------+-------+
|  3 |         Eric |    inactive |       3 |   8.0 |
+----+--------------+-------------+---------+-------+
|  5 |        Peggy |    inactive |       6 |   4.0 |
+----+--------------+-------------+---------+-------+
|  6 |         Earl |    inactive |       1 |   5.0 |
+----+--------------+-------------+---------+-------+
|  9 |        Jenny |    inactive |       4 |   8.0 |
+----+--------------+-------------+---------+-------+
| 11 |        Scott |    inactive |       2 |   4.0 |
+----+--------------+-------------+---------+-------+
| 12 |        James |    inactive |       1 |   8.0 |
+----+--------------+-------------+---------+-------+

Answer 1

一种解决方案是使用聚合，并将逻辑移动到 HAVING 子句。以下查询可以为您提供满足条件的客户姓名 : :

SELECT cust_name
FROM mytable
GROUP BY cust_name
HAVING COUNT(*) = 1 AND MAX(status) = 'inactive'

返回:

| cust_name |
| --------- |
| Earl      |
| Eric      |
| Jenny     |

如果您还想查看订单，那么您可以将其转换为子查询:

SELECT *
FROM mytable
WHERE cust_name IN (
    SELECT cust_name
    FROM mytable
    GROUP BY cust_name
    HAVING COUNT(*) = 1 AND MAX(status) = 'inactive'
)

结果:

| ID  | cust_name | status   | item_no | price |
| --- | --------- | -------- | ------- | ----- |
| 3   | Eric      | inactive | 3       | 8     |
| 6   | Earl      | inactive | 1       | 5     |
| 9   | Jenny     | inactive | 4       | 8     |

Demo on DB Fiddle

---

从 MySQL 8.0 开始，窗口函数使它变得更容易、更高效。您可以将它们内联以计算客户的订单总数以及非事件订单的数量；然后只适合两个值都为 1 的记录:

SELECT * FROM (
    SELECT 
        t.*, 
        COUNT(*) OVER(PARTITION BY cust_name) cnt_total, 
        SUM(status = 'inactive') OVER(PARTITION BY cust_name) cnt_inactive
    FROM mytable t
 ) x WHERE cnt_total = 1 AND cnt_inactive = 1;

Demo on DB Fiddle