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