这个查询该怎么写
TB表:
ccode ccus_id csup_id iperiod
--------------- -------------------- -------------------- -------
1111 0101 NULL 1
1111 0101 NULL 2
1001 NULL 0104 1
1111 0102 NULL 1
1111 0102 NULL 2
1111 0102 NULL 3
1002 NULL 0101 1
想得到:如果前三字段一样,统计count(iperiod)> 2,也就是
ccode ccus_id csup_id iperiod
--------------- -------------------- -------------------- -------
1111 0102 NULL 1
1111 0102 NULL 2
1111 0102 NULL 3
[解决办法]
select a.*
from tb a,(
select
ccode,ccus_id,csup_id
from tb group by ccode,ccus_id,csup_id
having count(iperiod ) > 2
) b
where (a.ccode=b.ccode or a.ccode is null and b.code is null)
and (a.ccus_id=b.ccus_id or a.ccus_id is null and b.ccus_id is null)
and (a.csup_id=b.csup_id or a.csup_id is null and b.csup_id is null)
[解决办法]
select a.*
from tablename a,
(select ccode,ccus_id,csup_id from tablename group by ccode,ccus_id,csup_id having count(1) > 2) b
where a.ccode = b.ccode and a.ccus_id = b.ccus_id and a.csup_id = b.csup_id
