如何删除通过聚集函数判断的记录
表T如下:
ID C B
1 1 a
2 1 b
3 1 b
4 2 a
5 2 a
6 2 b
我要删除这样的记录:C和B字段都相同的记录数小于2的记录(这是简单举例,实际上必须要用到count函数),也就是ID为1和6的两条记录。我可以找到这样的记录:
select C,B,count(*) from T group by C,B having count(*)<2
但不能返回标识字段ID,我想删除满足上述条件的字段就不知道该怎么办了。IN只能每次用于一个字段,会删除不想删的记录:
delete from T where C in (select C from T group by C,B having count(*)<2)
and B in (select B from T group by C,B having count(*)<2)
[解决办法]
delete from T t where exists(select from T where t.c=c and t.b=b group by C,B having count(*)<2)
[解决办法]
如果是范围那就只能用exists了,如果用in的话就麻烦了
delete from T where exists(select 1 from T as o where t.c=o.c and t.b=o.b
group by o.C,o.B having count(*)<2)