SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.ID = B.ID AND NOT EXISTS(SELECT 1 FROM C WHERE C.ID = B.ID ))
select a.* from a , b where a.id = b.id and b.id not in (select id from c)select a.* from a , b where a.id = b.id and not exists (select 1 from c where c.id = b.id)
YY的, 试试这个 A表(cola1,cola2) B表(colb1,colb2,colb3)select * from ( SELECT A.cola1,A.cola2,COUNT(*) AS CNT FROM A,B WHERE A.cola1 = B.colb1(+) AND A.cola2=1 GROUP BY A.cola1,A.cola2 UNION ALL SELECT A.cola1,A.cola2,COUNT(*) AS CNT FROM A,B WHERE A.cola1 = B.colb2(+) AND A.cola2=2 GROUP BY A.cola1,A.cola2 ) where cnt =0
select distinct a.* from a , b where a.id = b.id and not exists (select 1 from c where c.id = b.id)
SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.ID = B.ID AND NOT EXISTS(SELECT 1 FROM C WHERE C.ID = B.ID ))
select id from (select id from A,B where A.id = B.id) tab where id not exist (select id from C)
( select cloa1 from A,B where a.cola2=1 and A.cola1=B.colb1 union select cloa1 from A,B where a.cola2=2 and A.cola1=B.colb2 ) minus select cola1 from A
select a.* from a , b where a.id = b.id and b.id not in (select id from c)这个方法可行
select * from (select * from a INTERSECT select * from b) d, (select from b minus select * from c)e where e.id=d.id 类似于上述的查询
SELECT * FROM A WHERE A.id=B.id AND B.id NOT IN ( SELECT id FROM C )
SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.ID = B.ID AND NOT EXISTS(SELECT 1 FROM C WHERE C.ID = B.ID ))
where a.id = b.id and b.id not in (select id from c)select a.* from a , b
where a.id = b.id and not exists (select 1 from c where c.id = b.id)
B表(colb1,colb2,colb3)
如果cola2=1,那么cola1=colb1
如果cola2=2,那么cola1=colb2cola2=1:代表主记录,cola2=2:代表的是明细,主记录和明细记录在A表都有记录
B表记录了主记录和明细记录的关联,
主记录的cola1记录在B的colb1字段里
明细的cola1记录在B表的colb2字段里
现在我想查询没有明细的主记录A(1,1);
A(11,2);
A(22,2);
A(2,1);
A(22,2);
A(3,1);
B(1,11,'1的明细记录11');
B(1,22,'1的明细记录22');
B(2,22,'2的明细记录22');
上面的数据A(3,1)是我想要的结果
试试这个
A表(cola1,cola2)
B表(colb1,colb2,colb3)select *
from (
SELECT A.cola1,A.cola2,COUNT(*) AS CNT
FROM A,B
WHERE A.cola1 = B.colb1(+)
AND A.cola2=1
GROUP BY A.cola1,A.cola2
UNION ALL
SELECT A.cola1,A.cola2,COUNT(*) AS CNT
FROM A,B
WHERE A.cola1 = B.colb2(+)
AND A.cola2=2
GROUP BY A.cola1,A.cola2
) where cnt =0
where a.id = b.id
and not exists (select 1 from c where c.id = b.id)
( select cloa1 from A,B where a.cola2=1 and A.cola1=B.colb1
union
select cloa1 from A,B where a.cola2=2 and A.cola1=B.colb2
)
minus
select cola1 from A
where a.id = b.id and b.id not in (select id from c)这个方法可行
INTERSECT
select * from b) d,
(select from b
minus
select * from c)e
where e.id=d.id
类似于上述的查询
SELECT *
FROM A
WHERE A.id=B.id AND B.id NOT IN (
SELECT id
FROM C
)