无活动状态的SQL查询

Question

场景：我有一个表，其状态为活动或删除。假设我们谈论的是车主。表中的状态可以反映以下事实:以前有两个所有者(即-已删除的状态)，并且应该只有一个当前所有者在表中具有“活动”状态。

问题：我如何编写涉及这样一个表的查询，只包括没有当前所有者的结果(即，对于具有“活动”状态的汽车，没有行)？

只有在至少有1行符合上述条件的情况下，以下内容才会匹配。因此，它对我所寻求的是无效的：

测试查询1

SELECT *
FROM car_owners
WHERE status = 'deleted'

测试查询2

SELECT *
FROM car_owners
WHERE status != 'active'

Answer 1

我想你想：

select co.*
from car_owners co
where not exists (select 1
                  from car_owners co2
                  where co2.car_id = co.car_id and co2.status = 'active'
                 );

如果您只想要car_id，可以使用聚合：

select co.car_id
from car_owners co
group by co.car_id
having not max( status = 'active' );

您还可以将having表述为：

having min(status) = 'deleted'

Answer 2

为此，可以使用不是在运算符。

表结构和样本数据

​

CREATE table car (
  carid int, 
  ownerid int, 
  carstatus varchar(50)
);
INSERT INTO car 
VALUES 
  (1, 1, "active"), 
  (2, 3, "active"), 
  (3, 5, "active"), 
  (4, 11, "deleted"), 
  (4, 12, "deleted"), 
  (4, 23, "active"), 
  (5, 13, "deleted"), 
  (6, 35, "deleted"), 
  (6, 38, "deleted"), 
  (6, 53, "deleted"), 
  (6, 10, "active"), 
  (7, 13, "deleted"), 
  (8, 16, "deleted"); 

SQL查询

SELECT *
FROM   car
WHERE  carid NOT IN (SELECT carid
                     FROM   car
                     WHERE  carstatus = "active") 

输出

+--------+----------+-----------+
| carid  | ownerid  | carstatus |
+--------+----------+-----------+
|     5  |      13  | deleted   |
|     7  |      13  | deleted   |
|     8  |      16  | deleted   |
+--------+----------+-----------+

在线演示：http://sqlfiddle.com/#!9/0746c1/9/0