我有一张旅客表
travellers(id,full_name)
和另一张旅行社历史表
travellers_history(id,traveller_id,status)
travellers_history中的状态为数字字段
我想要所有旅行者的记录,这些旅行者在旅行者历史记录中有状态11的记录,但在旅行者历史中没有状态12的记录。如何在sql中实现这一点?
您可以统计"11"s多于"12"s的旅行者:
select th.traveller_id
from traveller_history th
group by th.traveller_id
having sum( status = 11 ) > sum( status = 12 )
加入表格,按旅行者分组,并在have子句中设置条件:
select t.id, t.full_name
from travellers t inner join travellers_history h
on h.traveller_id = t.id
group by t.id, t.full_name
having sum(h.status = 12) = 0 and sum(h.status = 11) > 0
或:
select t.id, t.full_name
from travellers t inner join travellers_history h
on h.traveller_id = t.id
where h.status in (11, 12)
group by t.id, t.full_name
having max(h.status) = 11