试下: select * from tb_name a where exists (select count(*) from tb_name b where b.pid=a.pid and b.status=1)>=1 and exists (select count(*) from tb_name b where b.pid=a.pid and b.status<>1)>=1 group by pid having count(*)>1
上面的有问题,改成这样试下:select a.* from tb_mul a,tb_mul b,tb_mul c where b.status=1 and c.status<>1 and a.pid=b.pid and a.pid=c.pid
执行楼上发现有问题 having count(*)>1 最后得到的是不重复的记录我需要的是重复的记录
搞错了,改成这样就可以:select a.* from tb_mul a where (select count(*) from tb_mul b where b.pid=a.pid and b.status=1)>=1 and (select count(*) from tb_mul b where b.pid=a.pid and b.status<>1)>=1
select * from tb_name a where exists (select count(*) from tb_name b where b.pid=a.pid and b.status=1)>=1 and
exists (select count(*) from tb_name b where b.pid=a.pid and b.status<>1)>=1 group by pid having count(*)>1
最后得到的是不重复的记录我需要的是重复的记录
搞错了,改成这样就可以:select a.* from tb_mul a where (select count(*) from tb_mul b where b.pid=a.pid and b.status=1)>=1 and
(select count(*) from tb_mul b where b.pid=a.pid and b.status<>1)>=1
如果你的id是非重复的,而又要显示所有的重复记录,则把上面的改下也是可以用的:
select distinct a.* from tb_mul a,tb_mul b,tb_mul cwhere b.status=1and c.status<>1and a.pid=b.pidand a.pid=c.pid
from 表结构 a
where exists (select 1 from 表结构 where status=1)
and exists (select 1 from 表结构 where status!=1)