要求:
求A.NAME,满足条件
A.NEME=B.NAME
B.ID=C.ID
并且C表中ID相同一组中不等于2结果:NEME
--------------
AAA
DDD
==============================================================================================================表A:NAME
-------------------------
AAA
BBB
CCC
DDD
表B:ID NAME
-----------------------------------
111 AAA
222 BBB
333 CCC
444 DDD
表CID CLASS
-----------------------------------
111 1
111 3
222 1
222 2
222 3
333 2
333 3
444 1
求A.NAME,满足条件
A.NEME=B.NAME
B.ID=C.ID
并且C表中ID相同一组中不等于2结果:NEME
--------------
AAA
DDD
==============================================================================================================表A:NAME
-------------------------
AAA
BBB
CCC
DDD
表B:ID NAME
-----------------------------------
111 AAA
222 BBB
333 CCC
444 DDD
表CID CLASS
-----------------------------------
111 1
111 3
222 1
222 2
222 3
333 2
333 3
444 1
这句是什么意思
AND
CLASS!=2
select name from A
where name in
(select name from B
where id in
(select id from C t
where not exists (select * from C where id=t.id and class=2)
)
);
1 with a as(
2 select 'AAA' NAME FROM DUAL
3 UNION ALL
4 SELECT 'BBB' FROM DUAL
5 UNION ALL
6 SELECT 'CCC' FROM DUAL
7 UNION ALL
8 SELECT 'DDD' FROM DUAL),
9 B AS(
10 SELECT 111 ID,'AAA' NAME FROM DUAL
11 UNION ALL
12 SELECT 222,'BBB' FROM DUAL
13 UNION ALL
14 SELECT 333,'CCC' FROM DUAL
15 UNION ALL
16 SELECT 444,'DDD' FROM DUAL),
17 C AS(
18 SELECT 111 ID,1 CLASS FROM DUAL
19 UNION ALL
20 SELECT 111,3 FROM DUAL
21 UNION ALL
22 SELECT 222,1 FROM DUAL
23 UNION ALL
24 SELECT 222,2 FROM DUAL
25 UNION ALL
26 SELECT 222,3 FROM DUAL
27 UNION ALL
28 SELECT 333,2 FROM DUAL
29 UNION ALL
30 SELECT 333,3 FROM DUAL
31 UNION ALL
32 SELECT 444,1 FROM DUAL)--以上为提供数据的语句
33 SELECT A.NAME FROM A,B
34 WHERE A.NAME=B.NAME
35* AND NOT EXISTS(SELECT 1 FROM C WHERE B.ID=C.ID AND C.CLASS=2)
36 /NAM
---
AAA
DDD
SELECT a.* FROM a WHERE EXISTS(SELECT 1 FROM B WHERE a.NAME=b.NAME AND NOT EXISTS(SELECT 1 FROM c WHERE class=2 AND B.ID=C.ID))