Which every asset_id grouping, as a group have order requirements (group), each group of 10 data (or perhaps the later meet the conditions of the less number),
[for]
1, asset_id and account_id association need to meet certain conditions, not all pure association;
2, according to the serial number for each group first chooses to satisfy his need account_id, 10 in each group data of account_id cannot appear in front of the group already exists inside the account_id,
I ask you a great god, and the SQL to how to write, I wrote the following just don't know how to continue,
In order to simplify, you can ignore the following SQL statements WHERE the back of the several conditions
The SELECT a.a sset_id asset_id,
B.a ccount_id account_id,
Amy polumbo roject_type project_type,
A.r noA,
B.r noB,
DENSE_RANK () OVER (ORDER BY a.r noA) AS rn0,
ROW_NUMBER () OVER (PARTITION BY a.r noA ORDER BY b.r noB ASC) rn1
The FROM (SELECT a. *, rownum AS rnoA
The FROM (SELECT iaq. Asset_id asset_id,
Iaq. Project_type project_type,
Iaq. End_date asset_end_date,
Iaq. What 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
The FROM intel_asset_queue iaq
LEFT the JOIN fron_loan_application fla
ON the fla. Id=iaq. Asset_id
WHERE 1=1
AND the fla. Inner_type=0
AND iaq ctr_status!=9
AND the fla. Loan_money & gt;=the fla. Already_money
AND the fla. Loan_status=4
AND the fla. The start_date & lt; SYSDATE - 1
AND the fla. End_date & gt; SYSDATE
The ORDER BY iaq. Create_time ASC, -- -- rules 1 + 4
Iaq. What DESC - rule 5
) a
WHERE rownum & lt;=100). A,
(SELECT *, b. rownum AS rnoB
The 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
The FROM intel_tender_account ita
LEFT the JOIN intel_tender_plan itp
ON itp. Id=ita. Plan_id
WHERE 1=1
AND ita ctr_status!=9
AND ita. Tender_start_date & lt; SYSDATE - 1
AND ita. Tender_end_date & gt; SYSDATE
AND ita. Usable_balance & gt;=100
The ORDER BY ita. Ctr_status DESC, ita. Create_time) b
WHERE rownum & lt; B=1000)
WHERE 1=1
AND a.u ser_id!=b.u ser_id
AND CASE
WHEN ABS (a.r emain_money - b.u sable_balance)=0 THEN
1
WHEN ABS (a.r emain_money - b.u sable_balance) & gt; THEN=100
1
WHEN ABS (a.r emain_money - b.u sable_balance) & lt; 100 AND
A.r emain_money & gt; 2 * 100 AND b.u sable_balance & gt; 2 * 100 THEN
1
The ELSE
0
END=1
AND NOT the EXISTS
(SELECT 1
The FROM intel_tender_match_queue t
WHERE t.a sset_id=a.a sset_id
AND t.a ccount_id=b.a ccount_id
AND tc reate_time & gt; SYSDATE - * 3) (1/1440)
AND (a. d. eadline, b.d eadline) IN
(SELECT DISTINCT ipr asset_deadline, ipr. Account_deadline
The FROM intel_prematch_rule ipr
WHERE ipr. Status=1
AND (ipr) group_on_time IS NULL OR ipr) group_on_time & lt; Sysdate)
AND (ipr) group_off_time IS NULL OR ipr) group_off_time & gt; Sysdate));
CodePudding user response:
[simplified description below]From A table to take 10 asset_id, each asset_id cartesian product associated 2 account_id order requirements (present), and data, not repeat
Detailed results see hope
A table
ASSET_ID rownum
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
Table B
ACCOUNT_ID rownum
1 1
2 2
3 3
4 4
5 5
Cartesian product association
ASSET_ID ACCOUNT_ID
1 1
1
21 3
1
41 5
2 1
2 2
2, 3,
2 4
2 5
3 1
3 2
3 3
3, 4,
3 5
... ... .
Expectation result 1 x2
ASSET_ID ACCOUNT_ID
1 1
1
22, 3,
2 4
3 5
CodePudding user response:
C1 c2-- -- -- -- -- --
1
22 1
The two keep only one data?
CodePudding user response: