WITH M1 AS (
SELECT 'LMJ'NM, 1 Z,3 K,2 J FROM DUAL UNION ALL
SELECT 'XMJ', 5,4,2 FROM DUAL UNION ALL
SELECT 'DBT', 1,2,4 FROM DUAL UNION ALL
SELECT 'XBT', 4,3,4 FROM DUAL
)
SELECT T1.CNT LMJ
,T2.CNT XMJ
,T3.CNT DBT
,T4.CNT XBT
FROM (
SELECT M1.NM
,T2.CNT
,T2.CNT * M1.K/M1.Z K
,T2.CNT * M1.J AS J
FROM M1
,(SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 33) T2
WHERE M1.NM = 'LMJ'
) T1
,(
SELECT M1.NM
,T2.CNT
,T2.CNT * M1.K/M1.Z K
,T2.CNT * M1.J AS J
FROM M1
,(SELECT LEVEL*5 CNT FROM DUAL CONNECT BY LEVEL <= 100/5) T2
WHERE M1.NM = 'XMJ'
) T2
,(
SELECT M1.NM
,T2.CNT
,T2.CNT * M1.K/M1.Z K
,T2.CNT * M1.J AS J
FROM M1
,(SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 50) T2
WHERE M1.NM = 'DBT'
) T3
,(
SELECT M1.NM
,T2.CNT
,T2.CNT * M1.K/M1.Z K
,T2.CNT * M1.J AS J
FROM M1
,(SELECT LEVEL*4 CNT FROM DUAL CONNECT BY LEVEL <= 100/4) T2
WHERE M1.NM = 'XBT'
AND T2.CNT * M1.J <= 320
) T4
WHERE T1.K + T2.K + T3.K + T4.K = 100
AND T1.CNT + T2.CNT + T3.CNT + T4.CNT = 100
AND T1.J + T2.J + T3.J + T4.J BETWEEN 240 AND 320
本題的關(guān)鍵是使用 CONNECT BY LEVEL <= 100 創(chuàng)造循環(huán),再用CROSS JOIN 進(jìn)行循環(huán),篩選結(jié)果后得出。
SELECT LEVEL*5 CNT FROM DUAL CONNECT BY LEVEL <= 100/5
SELECT LEVEL CNT FROM DUAL CONNECT BY LEVEL <= 10
可用于產(chǎn)生多行,帶序號(hào)的結(jié)果。
聯(lián)系客服