Oracle資料庫，想用1個檢索sql實作畫面表示想要的結果。最好寫的詳細些，跪謝了
uj5u.com熱心網友回復：
簡單行列轉換..with t1 as
(select 'groupid1' as group_id,9999 as seach_code from dual union all
select 'groupid2' as group_id,9999 as seach_code from dual union all
select 'groupid3' as group_id,9999 as seach_code from dual),
t2 as
(select 'groupid1' as group_id,'groupkey11' as group_key from dual union all
select 'groupid1' as group_id,'groupkey12' as group_key from dual union all
select 'groupid1' as group_id,'groupkey13' as group_key from dual union all
select 'groupid1' as group_id,'groupkey14' as group_key from dual union all
select 'groupid2' as group_id,'groupkey21' as group_key from dual union all
select 'groupid2' as group_id,'groupkey22' as group_key from dual union all
select 'groupid3' as group_id,'groupkey31' as group_key from dual),
t3 as
(select 'groupkey11' as group_key,'a1' con1,'b1' con2,'c1' con3,'d1' con4,'e1' con5 from dual union all
select 'groupkey12' as group_key,'a2' con1,'b2' con2,'' con3,'' con4,'' con5 from dual union all
select 'groupkey13' as group_key,'a3' con1,'b3' con2,'c3' con3,'d3' con4,'' con5 from dual union all
select 'groupkey14' as group_key,'a4' con1,'' con2,'' con3,'' con4,'' con5 from dual union all
select 'groupkey21' as group_key,'a5' con1,'b5' con2,'c5' con3,'' con4,'' con5 from dual union all
select 'groupkey22' as group_key,'a6' con1,'' con2,'' con3,'' con4,'' con5 from dual union all
select 'groupkey31' as group_key,'a7' con1,'b7' con2,'' con3,'' con4,'' con5 from dual)
SELECT GROUP_ID, MAX(DECODE(RN, 1, CON)) 條件1, MAX(DECODE(RN, 2, CON)) 條件2,
MAX(DECODE(RN, 3, CON)) 條件3
FROM (SELECT A.GROUP_ID, A.RN,
RTRIM(REGEXP_REPLACE(T3.CON1 || ',' || T3.CON2 || ',' ||
T3.CON3 || ',' || T3.CON4 || ',' ||
T3.CON5, '[,]+', ','), ',') AS CON
FROM (SELECT T1.GROUP_ID, T2.GROUP_KEY,
ROW_NUMBER() OVER(PARTITION BY T1.GROUP_ID ORDER BY T2.GROUP_KEY) RN
FROM T1
LEFT JOIN T2
ON T2.GROUP_ID = T1.GROUP_ID) A
LEFT JOIN T3
ON T3.GROUP_KEY = A.GROUP_KEY
WHERE A.RN <= 3)
GROUP BY GROUP_ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/97867.html
標籤：開發
下一篇：資料庫連接01033錯誤