mysql - SQL 查询没有值

Question

我有一张如下表：

------+-------------+------------+----------------+------------------------------------------------+
| id   | customer_id | date       | action_type_id | details                                        |
+------+-------------+------------+----------------+------------------------------------------------+
| 4225 |         324 | 2015-09-07 |              1 | Sent mail Malcolm Murrey                       |
| 6320 |         324 | 2017-05-08 |              3 | quotes for price.                              |
|  156 |         326 | 2013-07-25 |              3 | Site visit to price job                        |
|  943 |         326 | 2013-10-23 |              1 | Arranged visit for snags on panel              |
| 1135 |         326 | 2013-11-28 |              1 | Arranged visit for site mod                    |
| 1930 |         326 | 2014-04-15 |              2 | Quoted for new HMI                             |
| 2644 |         326 | 2014-10-20 |              2 | Sent email about pending quote for HMI         |
| 2821 |         326 | 2014-11-25 |              1 | Screen problem                                 |
| 2184 |         328 | 2014-07-21 |              1 | Sent email detailing services                  |
+------+-------------+------------+----------------+------------------------------------------------+

我试图找到customer_id没有 2 的客户action_type_id。在这种情况下是 324 和 328，但似乎无法弄清楚。

我在想我可以做类似的事情group by customer_id not having action_type_id = 2......非常感谢任何帮助。

标签： mysqlsql

Answer 1

我会使用聚合：

select customerid
from t
group by customerid
having sum(action_type_id = 2) = 0;

我发现对于这种类型的查询，使用group byandhaving对于您可能拥有的条件非常灵活（比如您想要 2 和 3，或者不是 2，而是 3 或 4）。