取100个asset_id（每个asset_id按顺序排列好），每个asset_id上笛卡尔关联10个account_id（每个account_id也按顺序排列好，asset_id和account_id的关联需要满足一定条件）
即进行分组，每个asset_id为一组（组有顺序要求），每组10条数据（也可能越往后满足条件的条数越少）。
【要求】
1、asset_id和account_id的关联需要满足一定条件，不是纯粹的全关联；
2、按序号每组优先挑选满足自己需要的account_id，每组10条数据中account_id不能出现前面的组里面已经存在的account_id。请教一下各位大神，这个SQL要怎么写。 我写到下面就不知道该如何继续了。
爲了簡化，可以忽略下面SQL語句WHERE後面的几个条件
SELECT a.asset_id asset_id,
b.account_id account_id,
a.project_type project_type,
a.rnoA,
b.rnoB,
DENSE_RANK() OVER(ORDER BY a.rnoA) AS rn0,
ROW_NUMBER() OVER(PARTITION BY a.rnoA ORDER BY b.rnoB ASC) rn1
FROM (SELECT a.*, rownum AS rnoA
FROM (SELECT iaq.asset_id asset_id,
iaq.project_type project_type,
iaq.end_date asset_end_date,
iaq.rate asset_rate,
fla.create_time asset_create_time,
fla.loan_money - fla.already_money remain_money,
iaq.status,
fla.deadline deadline,
fla.user_id user_id
FROM intel_asset_queue iaq
LEFT JOIN fron_loan_application fla
ON fla.id = iaq.asset_id
WHERE 1 = 1
AND fla.inner_type = 0
AND iaq.ctr_status != 9
AND fla.loan_money >= fla.already_money
AND fla.loan_status = 4
AND fla.start_date < SYSDATE - 1
AND fla.end_date > SYSDATE
ORDER BY iaq.create_time ASC, --规则1+规则4
iaq.rate DESC --规则5
) a
WHERE rownum <= 100) a,
(SELECT b.*, rownum AS rnoB
FROM (SELECT ita.id account_id,
ita.usable_balance usable_balance,
ita.end_date account_end_date,
ita.create_time account_create_time,
ita.join_money account_join_money,
ita.ctr_status,
itp.deadline,
ita.user_id
FROM intel_tender_account ita
LEFT JOIN intel_tender_plan itp
ON itp.id = ita.plan_id
WHERE 1 = 1
AND ita.ctr_status != 9
AND ita.tender_start_date < SYSDATE - 1
AND ita.tender_end_date > SYSDATE
AND ita.usable_balance >= 100
ORDER BY ita.ctr_status DESC, ita.create_time) b
WHERE rownum <= 1000) b
WHERE 1 = 1
AND a.user_id != b.user_id
AND CASE
WHEN ABS(a.remain_money - b.usable_balance) = 0 THEN
1
WHEN ABS(a.remain_money - b.usable_balance) >= 100 THEN
1
WHEN ABS(a.remain_money - b.usable_balance) < 100 AND
a.remain_money > 2 * 100 AND b.usable_balance > 2 * 100 THEN
1
ELSE
0
END = 1
AND NOT EXISTS
(SELECT 1
FROM intel_tender_match_queue t
WHERE t.asset_id = a.asset_id
AND t.account_id = b.account_id
AND t.create_time > SYSDATE - (1 / 1440 * 3))
AND (a.deadline, b.deadline) IN
(SELECT DISTINCT ipr.asset_deadline, ipr.account_deadline
FROM intel_prematch_rule ipr
WHERE ipr.status = 1
AND (ipr.group_on_time IS NULL OR ipr.group_on_time < sysdate)
AND (ipr.group_off_time IS NULL OR ipr.group_off_time > sysdate));
即进行分组，每个asset_id为一组（组有顺序要求），每组10条数据（也可能越往后满足条件的条数越少）。
【要求】
1、asset_id和account_id的关联需要满足一定条件，不是纯粹的全关联；
2、按序号每组优先挑选满足自己需要的account_id，每组10条数据中account_id不能出现前面的组里面已经存在的account_id。请教一下各位大神，这个SQL要怎么写。 我写到下面就不知道该如何继续了。
爲了簡化，可以忽略下面SQL語句WHERE後面的几个条件
SELECT a.asset_id asset_id,
b.account_id account_id,
a.project_type project_type,
a.rnoA,
b.rnoB,
DENSE_RANK() OVER(ORDER BY a.rnoA) AS rn0,
ROW_NUMBER() OVER(PARTITION BY a.rnoA ORDER BY b.rnoB ASC) rn1
FROM (SELECT a.*, rownum AS rnoA
FROM (SELECT iaq.asset_id asset_id,
iaq.project_type project_type,
iaq.end_date asset_end_date,
iaq.rate asset_rate,
fla.create_time asset_create_time,
fla.loan_money - fla.already_money remain_money,
iaq.status,
fla.deadline deadline,
fla.user_id user_id
FROM intel_asset_queue iaq
LEFT JOIN fron_loan_application fla
ON fla.id = iaq.asset_id
WHERE 1 = 1
AND fla.inner_type = 0
AND iaq.ctr_status != 9
AND fla.loan_money >= fla.already_money
AND fla.loan_status = 4
AND fla.start_date < SYSDATE - 1
AND fla.end_date > SYSDATE
ORDER BY iaq.create_time ASC, --规则1+规则4
iaq.rate DESC --规则5
) a
WHERE rownum <= 100) a,
(SELECT b.*, rownum AS rnoB
FROM (SELECT ita.id account_id,
ita.usable_balance usable_balance,
ita.end_date account_end_date,
ita.create_time account_create_time,
ita.join_money account_join_money,
ita.ctr_status,
itp.deadline,
ita.user_id
FROM intel_tender_account ita
LEFT JOIN intel_tender_plan itp
ON itp.id = ita.plan_id
WHERE 1 = 1
AND ita.ctr_status != 9
AND ita.tender_start_date < SYSDATE - 1
AND ita.tender_end_date > SYSDATE
AND ita.usable_balance >= 100
ORDER BY ita.ctr_status DESC, ita.create_time) b
WHERE rownum <= 1000) b
WHERE 1 = 1
AND a.user_id != b.user_id
AND CASE
WHEN ABS(a.remain_money - b.usable_balance) = 0 THEN
1
WHEN ABS(a.remain_money - b.usable_balance) >= 100 THEN
1
WHEN ABS(a.remain_money - b.usable_balance) < 100 AND
a.remain_money > 2 * 100 AND b.usable_balance > 2 * 100 THEN
1
ELSE
0
END = 1
AND NOT EXISTS
(SELECT 1
FROM intel_tender_match_queue t
WHERE t.asset_id = a.asset_id
AND t.account_id = b.account_id
AND t.create_time > SYSDATE - (1 / 1440 * 3))
AND (a.deadline, b.deadline) IN
(SELECT DISTINCT ipr.asset_deadline, ipr.account_deadline
FROM intel_prematch_rule ipr
WHERE ipr.status = 1
AND (ipr.group_on_time IS NULL OR ipr.group_on_time < sysdate)
AND (ipr.group_off_time IS NULL OR ipr.group_off_time > sysdate));
从A表中取10个asset_id，每个asset_id笛卡儿积关联2个account_id（存在顺序要求），取不重复的数据。详细结果见期望结果A表
ASSET_ID rownum
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10B表
ACCOUNT_ID rownum
1 1
2 2
3 3
4 4
5 5
笛卡儿积关联
ASSET_ID ACCOUNT_ID
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
2 5
3 1
3 2
3 3
3 4
3 5
… ….
期望结果 1x2
ASSET_ID ACCOUNT_ID
1 1
1 2
2 3
2 4
3 5