mysql - 如何计算出总不同值减去 MySQL 中的分组不同值

Question

给定样本表：

+----+----------+---------+------+
| id | userName | storeId | cost |
+----+----------+---------+------+
|  1 | foo      |       1 |   10 |
|  2 | bar      |       1 |   10 |
|  3 | baz      |       5 |    5 |
|  4 | baz      |       3 |   20 |
|  5 | qux      |       1 |    5 |
|  6 | qux      |       4 |   20 |
|  7 | qux      |      15 |   30 |
|  8 | qux      |      17 |   40 |
|  9 | qux      |       3 |    5 |
| 10 | quux     |       6 |   20 |
+----+----------+---------+------+

我想算出有多少人在每家商店购买了，有多少人没有购买。我希望报告显示按商店分组的结果。

我知道该语句select storeId, count(distinct username) as total from purchases group by storeId为我提供了在每家商店购买了多少人，但我想select count(distinct userName) from purchases;在另一列中减去查询结果。我希望示例输出显示如下。

+---------+-----------+--------------+
| storeId | purchased | notPurchased |
+---------+-----------+--------------+
|       1 |         3 |            2 |
|       3 |         2 |            3 |
|       4 |         1 |            4 |
|       5 |         1 |            4 |
|       6 |         1 |            4 |
|      15 |         1 |            4 |
|      17 |         1 |            4 |
+---------+-----------+--------------+

标签： mysql

Answer 1

您可以在 IN() 函数中使用 NOT 条件