分组聚合函数从1:1 、1:n、n:1、n:n筛选1:1

1.场景：两个相关联的表之间，有1:1、1:n、n:1、n:n等多种关系，从中筛选出1：1的数据。
表A：

表B：

方案：4次分组聚合函数从1:1、1:n、n:1、n:n 中找出 1:1的数据(SQL)：
SELECT
SUBTSK_NBR,
ACTUAL_NBR
FROM
(
SELECT
SUBTSK_NBR,
ACTUAL_NBR,
CREATE_TIME,
ROWNUMBER_1,
ROWNUMBER_2,
ROWNUMBER_3,
ROW_NUMBER() OVER (PARTITION BY E.ACTUAL_NBR
ORDER BY
E.SUBTSK_NBR ASC) AS ROWNUMBER_4
FROM
(
SELECT
SUBTSK_NBR,
ACTUAL_NBR,
CREATE_TIME,
ROWNUMBER_1,
ROWNUMBER_2,
ROW_NUMBER() OVER (PARTITION BY D.ACTUAL_NBR
ORDER BY
D.SUBTSK_NBR DESC) AS ROWNUMBER_3
FROM
(
SELECT
SUBTSK_NBR,
ACTUAL_NBR,
CREATE_TIME,
ROWNUMBER_1,
ROW_NUMBER() OVER (PARTITION BY C.SUBTSK_NBR
ORDER BY
C.CREATE_TIME ASC) AS ROWNUMBER_2
FROM
(
SELECT
A.SUBTSK_NBR,
B.ACTUAL_NBR,
B.CREATE_TIME,
ROW_NUMBER() OVER (PARTITION BY A.SUBTSK_NBR
ORDER BY
B.CREATE_TIME DESC) AS ROWNUMBER_1
FROM
UCS.CLR_WRITEOFF_SUBTASK A,
UCS.CLR_WRITEOFF_ACTUAL_PAYMENT B
WHERE
a.ACC_NBR = b.ACC_NBR
AND a.WRITEOFF_DIRECT = ‘AR’
AND b.WRITEOFF_DIRECT = ‘AR’
AND ABS(a.TRS_AMT - b.TRS_AMT)<100000
AND a.SUBTSK_STATE = ‘WAIT’
AND b.SUBTSK_STATE = ‘WAIT’
AND a.RECEIVER_FLAG = ‘NUL’
AND b.RECEIVER_FLAG = ‘NUL’
AND(a.associated_id NOT LIKE ‘CHILDREN%’
OR A.associated_id IS NULL) ) C ) D ) E )
WHERE
ROWNUMBER_1 = 1
AND ROWNUMBER_2 = 1
AND ROWNUMBER_3 = 1
AND ROWNUMBER_4 = 1;

思路：第1次先以A表主键为分组 B表时间降序分组排序，第2次先以A表主键为分组 B表时间升序分组排序（参考排序的列不能与参考分组的列有关系）；第3次先以B表主键为分组 A表主键降序分组排序,第3次先以B表主键为分组 A表主键升序序分组排序，最后取出四个number为1的。