现在数据库存有二张表,test_a和test_b,
当test_b存了以下六条数据
我就要显现
--------------
中国 广东省
中国 江西省 南昌市
---------------
如果把test_b表去掉a_id = 1的一条记录时,查询就要显现
--------------
中国 广东省 深圳市
中国 广东省 惠州市
中国 广东省 清远市
中国 广东省 东莞市
中国 江西省 南昌市--------------当test_b表中存在a表所有的ID,查询就要显现------------
中国
------------
SQL> select * from test_a; A_ID STATEMENT1 STATEMENT2 STATEMENT3
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 中国 广东省 广州市
2 中国 广东省 深圳市
3 中国 广东省 惠州市
4 中国 广东省 清远市
5 中国 广东省 东莞市
6 中国 江西省 南昌市
7 中国 江西省 九江市
8 中国 江西省 赣州市
9 中国 江西省 萍乡市
10 中国 江西省 抚州市10 rows selectedSQL> select * from test_b; B_ID A_ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 66 rows selected
当test_b存了以下六条数据
我就要显现
--------------
中国 广东省
中国 江西省 南昌市
---------------
如果把test_b表去掉a_id = 1的一条记录时,查询就要显现
--------------
中国 广东省 深圳市
中国 广东省 惠州市
中国 广东省 清远市
中国 广东省 东莞市
中国 江西省 南昌市--------------当test_b表中存在a表所有的ID,查询就要显现------------
中国
------------
SQL> select * from test_a; A_ID STATEMENT1 STATEMENT2 STATEMENT3
---------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 中国 广东省 广州市
2 中国 广东省 深圳市
3 中国 广东省 惠州市
4 中国 广东省 清远市
5 中国 广东省 东莞市
6 中国 江西省 南昌市
7 中国 江西省 九江市
8 中国 江西省 赣州市
9 中国 江西省 萍乡市
10 中国 江西省 抚州市10 rows selectedSQL> select * from test_b; B_ID A_ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 66 rows selected
但是当test_b表中,关系了test_a表所有广东省记录,也就是五条记录我所要查询出来的记录就是一条记录
---------
中国 广东省 null
---------
Connected as dc_etlSQL> CREATE TABLE TEST_A AS
2 select 1 AS A_ID,'中国' AS STATEMENT1, '广东省' AS STATEMENT2, '广州市' AS STATEMENT3 FROM DUAL UNION
3 select 2 AS A_ID,'中国', '广东省', '深圳市' FROM DUAL UNION
4 select 3 AS A_ID,'中国', '广东省', '惠州市' FROM DUAL UNION
5 select 4 AS A_ID,'中国', '广东省', '清远市' FROM DUAL UNION
6 select 5 AS A_ID,'中国', '广东省', '东莞市' FROM DUAL UNION
7 select 6 AS A_ID,'中国', '江西省', '南昌市' FROM DUAL UNION
8 select 7 AS A_ID,'中国', '江西省', '九江市' FROM DUAL UNION
9 select 8 AS A_ID,'中国', '江西省', '赣州市' FROM DUAL UNION
10 select 9 AS A_ID,'中国', '江西省', '萍乡市' FROM DUAL UNION
11 select 10 AS A_ID,'中国', '江西省', '抚州市' FROM DUAL ;
Table created
SQL>
SQL> CREATE TABLE TEST_B AS
2 select 1 AS B_ID,1 AS A_ID FROM DUAL UNION
3 select 2 AS B_ID,2 AS A_ID FROM DUAL UNION
4 select 3 AS B_ID,3 AS A_ID FROM DUAL UNION
5 select 4 AS B_ID,4 AS A_ID FROM DUAL UNION
6 select 5 AS B_ID,5 AS A_ID FROM DUAL UNION
7 select 6 AS B_ID,6 AS A_ID FROM DUAL ;
Table created
SQL>
SQL> SELECT DISTINCT STATEMENT1,
2 STATEMENT2,
3 STATEMENT3
4 FROM (SELECT A_ID,
5 STATEMENT1,
6 CASE
7 WHEN A_CT_ALL = B_CT_ALL THEN
8 NULL
9 ELSE
10 STATEMENT2
11 END AS STATEMENT2,
12 CASE
13 WHEN A_CT = B_CT THEN
14 NULL
15 ELSE
16 STATEMENT3
17 END AS STATEMENT3
18 FROM (SELECT A.*,
19 B.B_ID,
20 COUNT(A.A_ID) OVER(PARTITION BY A.STATEMENT2) AS A_CT,
21 SUM(DECODE(B_ID,NULL,0,1)) OVER(PARTITION BY A.STATEMENT2) AS B_CT,
22 COUNT(A.A_ID) OVER() AS A_CT_ALL,
23 SUM(DECODE(B_ID,NULL,0,1)) OVER() AS B_CT_ALL
24 FROM TEST_A A,TEST_B B
25 WHERE A.A_ID = B_ID(+)
26 ORDER BY A.A_ID)
27 WHERE B_ID IS NOT NULL---
28 )
29 ;
STATEMENT1 STATEMENT2 STATEMENT3
---------- ---------- ----------
中国 广东省
中国 江西省 南昌市
SQL> DROP TABLE TEST_B;
Table dropped
SQL>
SQL> CREATE TABLE TEST_B AS
2 select 2 AS B_ID,2 AS A_ID FROM DUAL UNION
3 select 3 AS B_ID,3 AS A_ID FROM DUAL UNION
4 select 4 AS B_ID,4 AS A_ID FROM DUAL UNION
5 select 5 AS B_ID,5 AS A_ID FROM DUAL UNION
6 select 6 AS B_ID,6 AS A_ID FROM DUAL ;
Table created
SQL>
SQL> SELECT DISTINCT STATEMENT1,
2 STATEMENT2,
3 STATEMENT3
4 FROM (SELECT A_ID,
5 STATEMENT1,
6 CASE
7 WHEN A_CT_ALL = B_CT_ALL THEN
8 NULL
9 ELSE
10 STATEMENT2
11 END AS STATEMENT2,
12 CASE
13 WHEN A_CT = B_CT THEN
14 NULL
15 ELSE
16 STATEMENT3
17 END AS STATEMENT3
18 FROM (SELECT A.*,
19 B.B_ID,
20 COUNT(A.A_ID) OVER(PARTITION BY A.STATEMENT2) AS A_CT,
21 SUM(DECODE(B_ID,NULL,0,1)) OVER(PARTITION BY A.STATEMENT2) AS B_CT,
22 COUNT(A.A_ID) OVER() AS A_CT_ALL,
23 SUM(DECODE(B_ID,NULL,0,1)) OVER() AS B_CT_ALL
24 FROM TEST_A A,TEST_B B
25 WHERE A.A_ID = B_ID(+)
26 ORDER BY A.A_ID)
27 WHERE B_ID IS NOT NULL---
28 )
29 ;
STATEMENT1 STATEMENT2 STATEMENT3
---------- ---------- ----------
中国 江西省 南昌市
中国 广东省 深圳市
中国 广东省 惠州市
中国 广东省 清远市
中国 广东省 东莞市
SQL> drop table test_b;
Table dropped
SQL>
SQL> CREATE TABLE TEST_B AS
2 select 1 AS B_ID,1 AS A_ID FROM DUAL UNION
3 select 2 AS B_ID,2 AS A_ID FROM DUAL UNION
4 select 3 AS B_ID,3 AS A_ID FROM DUAL UNION
5 select 4 AS B_ID,4 AS A_ID FROM DUAL UNION
6 select 5 AS B_ID,5 AS A_ID FROM DUAL UNION
7 select 6 AS B_ID,6 AS A_ID FROM DUAL UNION
8 select 7 AS B_ID,7 AS A_ID FROM DUAL UNION
9 select 8 AS B_ID,8 AS A_ID FROM DUAL UNION
10 select 9 AS B_ID,9 AS A_ID FROM DUAL UNION
11 select 10 AS B_ID,10 AS A_ID FROM DUAL ;
Table created
SQL>
SQL> SELECT DISTINCT STATEMENT1,
2 STATEMENT2,
3 STATEMENT3
4 FROM (SELECT A_ID,
5 STATEMENT1,
6 CASE
7 WHEN A_CT_ALL = B_CT_ALL THEN
8 NULL
9 ELSE
10 STATEMENT2
11 END AS STATEMENT2,
12 CASE
13 WHEN A_CT = B_CT THEN
14 NULL
15 ELSE
16 STATEMENT3
17 END AS STATEMENT3
18 FROM (SELECT A.*,
19 B.B_ID,
20 COUNT(A.A_ID) OVER(PARTITION BY A.STATEMENT2) AS A_CT,
21 SUM(DECODE(B_ID,NULL,0,1)) OVER(PARTITION BY A.STATEMENT2) AS B_CT,
22 COUNT(A.A_ID) OVER() AS A_CT_ALL,
23 SUM(DECODE(B_ID,NULL,0,1)) OVER() AS B_CT_ALL
24 FROM TEST_A A,TEST_B B
25 WHERE A.A_ID = B_ID(+)
26 ORDER BY A.A_ID)
27 WHERE B_ID IS NOT NULL---
28 )
29 ;
STATEMENT1 STATEMENT2 STATEMENT3
---------- ---------- ----------
中国