トップページに戻る
次のmodel句のサンプルへ
前のmodel句のサンプルへ
model句22 MySQLのgroup_concatもどき
SQLパズル
groupConcatT
ID Val
-- ---
1 AA
1 BB
1 CC
2 DD
2 EE
2 FF
3 GG
3 HH
3 II
4 JJ
5 a
5 b
5 c
5 d
5 e
5 f
MySQLのgroup_concatを使った下記の結果をmodel句を使って取得する。
select ID,group_concat(Val order by Val) as concatVal
from groupConcatT
group by ID;
出力結果
ID concatVal
-- -----------
1 AA,BB,CC
2 DD,EE,FF
3 GG,HH,II
4 JJ
5 a,b,c,d,e,f
データ作成スクリプト
create table groupConcatT(ID,Val) as
select 1,'AA' from dual union all
select 1,'BB' from dual union all
select 1,'CC' from dual union all
select 2,'DD' from dual union all
select 2,'EE' from dual union all
select 2,'FF' from dual union all
select 3,'GG' from dual union all
select 3,'HH' from dual union all
select 3,'II' from dual union all
select 4,'JJ' from dual union all
select 5,'a' from dual union all
select 5,'b' from dual union all
select 5,'c' from dual union all
select 5,'d' from dual union all
select 5,'e' from dual union all
select 5,'f' from dual;
SQL
--■■■添字の0番に集める方法■■■
select ID,substr(concatVal,2) as concatVal
from groupConcatT
model return updated rows
partition by(ID)
dimension by(Row_Number() over(partition by ID order by Val) as soeji)
measures(Val,cast(null as varchar2(20)) as concatVal)
rules iterate(100) UNTIL (presentV(Val[ITERATION_NUMBER+2],1,0) = 0)
(concatVal[0] = concatVal[0] || ',' || Val[ITERATION_NUMBER+1])
order by ID;
--■■■添字の1番に集める方法■■■
select ID,substr(concatVal,2) as concatVal
from groupConcatT
model return updated rows
partition by(ID)
dimension by(Row_Number() over(partition by ID order by Val) as soeji)
measures(Val,cast(null as varchar2(20)) as concatVal)
rules iterate(100) UNTIL (presentV(Val[ITERATION_NUMBER+2],1,0) = 0)
(concatVal[1] = concatVal[1] || ',' || Val[ITERATION_NUMBER+1])
order by ID;
解説