可以试试这些方法:
select *
from a
where exists(select 1
from b
where b.x = a.x
and b.xx = a.xx
having count(*) = 1
AND MAX(b.xxx )= 'sdfsd')
and exists(select 1
from c
where c.y = a.y
and c.yy = a.yy
having count(*) > 1);
EXISTS 改为连接:
select a.*
from a
,(select a.ROWID ROWID_2
from b,a
where b.x = a.x
and b.xx = a.xx
GROUP BY a.ROWID
having count(*) = 1
AND MAX(b.xxx )= 'sdfsd'
) a2
,(select a.ROWID ROWID_3
from c,a
where c.y = a.y
and c.yy = a.yy
GROUP BY a.ROWID
having count(*) > 1
) a3
WHERE a.ROWID = a2.ROWID_2 AND a.ROWID = a3.ROWID_3
或者:
select a.*
from a
,(select b.x,b.xx
from b
GROUP BY b.x,b.xx
having count(*) = 1
AND MAX(b.xxx )= 'sdfsd'
) a2
,(select c.y,c.yy
from c
GROUP BY c.y,c.yy
having count(*) > 1
) a3
WHERE a.x=a2.x AND a.xx=a2.xx
AND a.y=a3.y AND a.yy=a3.yy
或者上述方法的混合,看看哪个效果最好。
|