- 论坛徽章:
- 9
|
原帖由 liyihongcug 于 2009-8-27 17:53 发表 ![]()
找符合这样条件的记录不:
多个记录具有相同的pId,
其中至少1条的Status为1,而且至少一条的Status不为1。
这个语句如何写
表结构 (id,pid,status)
如何做这个sql
写了两条,看哪条效率高吧:
select * from
stat
inner join
(
select pid from stat group by pid
having sum(if(status=1,1,0))>0
and
sum(if(status<>1,1,0))>0
) as tmp
on stat.pid = tmp.pid ; |
select * from stat as st1
where
(select count(*) from stat as st2 where st2.pid=st1.pid and st2.status <>1) >= 1
and
(select count(*) from `stat` as st3 where st3.pid=st1.pid and st3.status=1 ) >=1; |
|
|