求SOL写法:
表A(AC1,BC2.........)AC1是主键、BC2是唯一索引。
表B(BC1,BC2.........)BC1是主键、BC2可能有多条记录、BC2也是索引。表A数据(300多万行):
AC1 BC2
------------
A1 AB1
A2 AB2
A3 AB3
A4 AB4
表B数据:(800多万行)
BC1 BC2
---------
B1 AB1
B2 AB1
B3 AB2
B4 AB1求结果集(下面两个结果集都满足要求)【左连接:A.BC2=B.BC2(+)、如果表A对应表B有多条记录的话,随便一条记录的s1为1,其他为零。】
AC1 BC2 BC1 s1 s2
----------------------
A1 AB1 B1 1 1
A1 AB1 B2 0 1
A1 AB1 B4 0 1
A2 AB2 B3 1 1
A3 AB3 null 1 0
A4 AB4 null 1 0
或
AC1 BC2 BC1 s1 s2
----------------------
A1 AB1 B1 0 1
A1 AB1 B2 1 1
A1 AB1 B4 0 1
A2 AB2 B3 1 1
A3 AB3 null 1 0
A4 AB4 null 1 0
或
AC1 BC2 BC1 s1 s2
----------------------
A1 AB1 B1 0 1
A1 AB1 B2 0 1
A1 AB1 B4 1 1
A2 AB2 B3 1 1
A3 AB3 null 1 0
A4 AB4 null 1 0求SOL写法和优化这个SOL。
表A(AC1,BC2.........)AC1是主键、BC2是唯一索引。
表B(BC1,BC2.........)BC1是主键、BC2可能有多条记录、BC2也是索引。表A数据(300多万行):
AC1 BC2
------------
A1 AB1
A2 AB2
A3 AB3
A4 AB4
表B数据:(800多万行)
BC1 BC2
---------
B1 AB1
B2 AB1
B3 AB2
B4 AB1求结果集(下面两个结果集都满足要求)【左连接:A.BC2=B.BC2(+)、如果表A对应表B有多条记录的话,随便一条记录的s1为1,其他为零。】
AC1 BC2 BC1 s1 s2
----------------------
A1 AB1 B1 1 1
A1 AB1 B2 0 1
A1 AB1 B4 0 1
A2 AB2 B3 1 1
A3 AB3 null 1 0
A4 AB4 null 1 0
或
AC1 BC2 BC1 s1 s2
----------------------
A1 AB1 B1 0 1
A1 AB1 B2 1 1
A1 AB1 B4 0 1
A2 AB2 B3 1 1
A3 AB3 null 1 0
A4 AB4 null 1 0
或
AC1 BC2 BC1 s1 s2
----------------------
A1 AB1 B1 0 1
A1 AB1 B2 0 1
A1 AB1 B4 1 1
A2 AB2 B3 1 1
A3 AB3 null 1 0
A4 AB4 null 1 0求SOL写法和优化这个SOL。
2 (
3 SELECT 'A1' AS AC1, 'AB1' AS BC2 FROM DUAL UNION
4 SELECT 'A2' AS AC1, 'AB2' AS BC2 FROM DUAL UNION
5 SELECT 'A3' AS AC1, 'AB3' AS BC2 FROM DUAL UNION
6 SELECT 'A4' AS AC1, 'AB4' AS BC2 FROM DUAL
7 ),
8 B AS
9 (
10 SELECT 'B1' AS BC1, 'AB1' AS BC2 FROM DUAL UNION
11 SELECT 'B2' AS BC1, 'AB1' AS BC2 FROM DUAL UNION
12 SELECT 'B3' AS BC1, 'AB2' AS BC2 FROM DUAL UNION
13 SELECT 'B4' AS BC1, 'AB1' AS BC2 FROM DUAL
14 )
15 SELECT A.AC1,
16 A.BC2,
17 B.BC1,
18 DECODE(ROW_NUMBER() OVER(PARTITION BY A.BC2 ORDER BY B.BC1), 1, 1, 0) AS S1,
19 NVL2(B.BC1, 1, 0) AS S2
20 FROM A,
21 B
22 WHERE A.BC2 = B.BC2(+) ---
23 ;
AC1 BC2 BC1 S1 S2
--- --- --- ---------- ----------
A1 AB1 B1 1 1
A1 AB1 B2 0 1
A1 AB1 B4 0 1
A2 AB2 B3 1 1
A3 AB3 1 0
A4 AB4 1 0
6 rows selected
SQL>
是sql写法吗?