sql: case when

 

表主要结构：

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