我是要用在Oracle上，如果有SQL的方式也可以，謝謝。
目前只試出所有sn欄位合併
但我需要每5筆合並一次 (1-5，6-10、11-15~~~依此類推)
還請高手幫忙指點
table如下
下方黃色區塊為每5筆合併後的
已邀請的邦友 {{ invite_list.length }}/5
這個我用MSSQL寫的~你拿去套看看~
declare @tab table(
item int
,num int
,sn varchar(5)
)
insert into @tab
values(1,11,'a')
,(2,11,'b')
,(3,11,'c')
,(4,11,'d')
,(5,11,'e')
,(6,11,'f')
,(7,11,'g')
,(8,11,'h')
,(9,11,'i')
,(10,11,'j')
,(11,11,'k')
,(12,11,'l')
,(13,11,'n')
,(14,11,'m')
,(15,11,'o')
select *
,stuff((
select ',' + sn
from @tab b
where b.item between ((a.item - 1) / 5) * 5 + 1
and ((a.item - 1) / 5 + 1) * 5
for xml path('')
),1,1,'') as Sn_Str
from @tab a
補上Oracle版
select a.*
,(
select listagg(b.sn,',') within GROUP (order by b.sn)
from tab b
where b.item between (TRUNC((a.item - 1) / 5,0)) * 5 + 1
and (TRUNC((a.item - 1) / 5,0) + 1) * 5
) as Sn_Str
from tab a;
大大太狂了，大感謝，已經弄成我要的了。
在請問一下大大，這個做法有沒有辦法知道加了幾次例如
若沒加滿要有餘數的值，如圖最後一欄
你就二次查詢不等於5就是了~
我第二次 用COUNT的方法會算出總筆數...
應該是方法用錯，算到總筆數
你不是要列出後面三筆?
select *
from (
select *
,stuff((
select ',' + sn
from @tab b
where b.item between ((a.item - 1) / 5) * 5 + 1
and ((a.item - 1) / 5 + 1) * 5
for xml path('')
),1,1,'') as Sn_Str
,isNull((
select count(*)
from @tab b
where b.item between ((a.item - 1) / 5) * 5 + 1
and ((a.item - 1) / 5 + 1) * 5
),0) as Sn_Num
from @tab a
) as k
where Sn_Num <> 5
誤會意思了
我是要在後面加一欄add_qty
我用COUNT(1) 只能算出總筆數
有沒有什麼含數可以算數 合併SN的次數
沒有函數呢~要用len去計算數量~
select *
,len(Sn_Str) - len(Replace(Sn_Str,',','')) + 1 as Sn_Num
from (
select *
,stuff((
select ',' + sn
from @tab b
where b.item between ((a.item - 1) / 5) * 5 + 1
and ((a.item - 1) / 5 + 1) * 5
for xml path('')
),1,1,'') as Sn_Str
from @tab a
) as k
我用SQLite寫的
create table tb (item integer, num integer, sn text);
insert into tb (item, num, sn) values (1,11,'a'),(2,11,'b'),(3,11,'c'),(4,11,'d'),(5,11,'e'),(6,11,'f'),(7,11,'g'),(8,11,'h'),(9,11,'i'),(10,11,'j'),(11,11,'k'),(12,11,'L');
select tb.*, m1||m2||m3||m4||m5 as sn_total
from tb left join(
select
main.q
, coalesce(a.sn||',','') as m1
, coalesce(b.sn||',','') as m2
, coalesce(c.sn||',','') as m3
, coalesce(d.sn||',','') as m4
, coalesce(e.sn||',','') as m5
from
(select (item -1)/5 as q from tb group by q) main
left join (
select (item -1)/5 as q, sn
from tb where (item - (item -1)/5*5) = 1
) a on main.q=a.q
left join (
select (item -1)/5 as q, sn
from tb where (item - (item -1)/5*5) = 2
) b on main.q=b.q
left join (
select (item -1)/5 as q, sn
from tb where (item - (item -1)/5*5) = 3
) c on main.q=c.q
left join (
select (item -1)/5 as q, sn
from tb where (item - (item -1)/5*5) = 4
) d on main.q=d.q
left join (
select (item -1)/5 as q, sn
from tb where (item - (item -1)/5*5) = 5
) e on main.q=e.q
) g
on (tb.item - 1) / 5 = g.q
應該類似這樣的結果吧？
為方便說明，我拆成A~D層，熟悉的話C和D層可以併一起
select D.*,
COALESCE(tag8,'')||COALESCE(tag9,'')||COALESCE(tag10,'')||COALESCE(tag11,'')||COALESCE(tag12,'') goal,
MAX(tag2) over (PARTITION BY tag1) add_qty
--------以下D層--------
from(
select C.*,
MAX(tag3) over (PARTITION BY tag1) tag8,
MAX(tag4) over (PARTITION BY tag1) tag9,
MAX(tag5) over (PARTITION BY tag1) tag10,
MAX(tag6) over (PARTITION BY tag1) tag11,
MAX(tag7) over (PARTITION BY tag1) tag12
--------以下C層--------
from(
select B.*,
CASE WHEN tag2 =1 THEN sn ELSE null END tag3,
CASE WHEN tag2 =2 THEN sn ELSE null END tag4,
CASE WHEN tag2 =3 THEN sn ELSE null END tag5,
CASE WHEN tag2 =4 THEN sn ELSE null END tag6,
CASE WHEN tag2 =5 THEN sn ELSE null END tag7
--------以下B層--------
from(
select A.*,
(item::numeric-tag1)+5 tag2
--------以下A層--------
from(
SELECT *,
(CEILING((item::numeric)/5))*5 tag1
FROM "ithelp_case27"
) A
--------以上A層--------
) B
--------以上B層--------
) C
--------以上C層--------
) D
--------以上D層--------
order by item::numeric
A層
tag1：先建出你要的區間區塊，寫法不只一種，我是用無條件進位去做到。
B層
tag2：把每個區間區塊的1~5給做出來，有相同規律後面才好處理，寫法不只一種。
C層
tag3~tag7：這只能這樣處理，一定得做出5個欄位。
多說幾句，如果你想改成每6筆、每7筆合併，這串語法做不到，必須寫成function迴圈，有需要我再寫上來。但這串語法，可以處理【每5筆合併的無限筆資料】。
D層
tag8~tag12：透過 over partition，把最終目標的前一步做出來，下MIN或MAX都一樣，反正null不會被拿來比。
我是用postgreSQL，你最裡層的from改一下，另外我做這資料匯入時都建成文字所以有::numeric的處理，你再酌情修掉就好。
iThome鐵人賽
看影片追技術