表主要结构:
a { id, type, data }
b { id, b_type, a_id, data }
当b.b_type为0时,b.a_id对应于a.id;
当b.b_type为1时,b.a_id对应a.type。。。
以下语句用于查看重复数据,
#极其没效率的 in版本
select * from a_b
where a_id in (
select a_id
from a_b
group by a_id having count(distinct b_type) = 1
) and b_type = 0
order by a_id, b_type
#group by, case when
select a_id, count(case b_type when 0 then 1 end) as type_0, count(case b_type when 1 then 1 end) as type_1
from a_b
group by a_id having type_1 = 0 and type_0 > 0 #count(distinct b_type) = 1 and
#having条件 和 子查询where 作用相当
select * from (
select a_id, count(case b_type when 0 then 1 end) as type_0, count(case b_type when 1 then 1 end) as type_1
from a_b
group by a_id
) as t where type_1 = 0