トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-291 累計(重複値あり)を求めるwmsys.wm_concatもどき
SQLパズル
workT
ID Val
-- ---
1 AAA
1 BBB
1 CCC
1 CCC
1 DDD
2 EEE
2 EEE
2 EEE
2 EEE
2 FFF
2 FFF
2 GGG
select ID,Val,
wmsys.wm_concat(Val)
over(partition by ID order by Val) as conStr
from workT;
と同じ結果を取得する。
出力結果
ID Val conStr
-- --- ---------------------------
1 AAA AAA
1 BBB AAA,BBB
1 CCC AAA,BBB,CCC,CCC
1 CCC AAA,BBB,CCC,CCC
1 DDD AAA,BBB,CCC,CCC,DDD
2 EEE EEE,EEE,EEE,EEE
2 EEE EEE,EEE,EEE,EEE
2 EEE EEE,EEE,EEE,EEE
2 EEE EEE,EEE,EEE,EEE
2 FFF EEE,EEE,EEE,EEE,FFF,FFF
2 FFF EEE,EEE,EEE,EEE,FFF,FFF
2 GGG EEE,EEE,EEE,EEE,FFF,FFF,GGG
データ作成スクリプト
create table workT(ID,Val) as
select 1,'AAA' from dual union all
select 1,'BBB' from dual union all
select 1,'CCC' from dual union all
select 1,'CCC' from dual union all
select 1,'DDD' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'FFF' from dual union all
select 2,'FFF' from dual union all
select 2,'GGG' from dual;
SQL
col conStr for a50
--■■■階層問い合わせを使う方法1■■■
select ID,aVal,
substr(sys_connect_by_path(bVal,','),2) as conStr
from (select a.ID,a.Val as aVal,b.Val as bVal,
a.RowID as Row_ID,
Row_Number()
over(partition by a.RowID order by b.Val) as rn
from workT a,workT b
where a.ID = b.ID
and a.Val >= b.Val)
where connect_by_IsLeaf = 1
Start With rn=1
connect by prior rn+1 = rn
and prior Row_ID = Row_ID
order by ID,aVal;
--■■■階層問い合わせを使う方法2■■■
select ID,Val,max(conStr) over(partition by ID,Val) as conStr
from (select ID,Val,
substr(sys_connect_by_path(Val,','),2) as conStr
from (select ID,Val,
Row_Number()
over(partition by ID order by Val) as rn
from workT)
Start With rn=1
connect by prior rn+1 = rn
and prior ID = ID)
order by ID,Val;
--■■■model句を使う方法1(RPad関数を使用)■■■
select ID,Val,substr(conStr,2) as conStr
from workT
model
partition by(ID)
dimension by(Row_Number()
over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr,
sum(Length(Val)+1) over(partition by ID order by Val) as LenB)
rules(
conStr[any] order by rn = RPad(conStr[cv()-1] || ',' || Val[cv()],LenB[cv()]
,',' || Val[cv()]));
--■■■model句を使う方法2(分析関数のmax関数を使用)■■■
select ID,Val,substr(conStr,2) as conStr
from workT
model
partition by(ID)
dimension by(Row_Number()
over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr)
rules(
conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()],
conStr[any] = max(conStr) over(partition by Val));
解説