iT邦幫忙

1

SQL / Oracle 每5筆合併欄位一次 如何執行?

我是要用在Oracle上,如果有SQL的方式也可以,謝謝。

目前只試出所有sn欄位合併
但我需要每5筆合並一次 (1-5,6-10、11-15~~~依此類推)
還請高手幫忙指點

table如下
https://ithelp.ithome.com.tw/upload/images/20180927/20082456fjuH5mKVqE.jpg

下方黃色區塊為每5筆合併後的

https://ithelp.ithome.com.tw/upload/images/20180927/20082456ENSVc5W3EN.jpg

3
純真的人
iT邦高手 1 級 ‧ 2018-09-27 17:30:07
最佳解答

這個我用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

https://ithelp.ithome.com.tw/upload/images/20180927/20061369z0QLJwx6sf.png

補上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;
看更多先前的回應...收起先前的回應...

大大太狂了,大感謝,已經弄成我要的了。

在請問一下大大,這個做法有沒有辦法知道加了幾次例如
若沒加滿要有餘數的值,如圖最後一欄

https://ithelp.ithome.com.tw/upload/images/20180928/20082456FqOQBrHpOP.jpg

你就二次查詢不等於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的次數

https://ithelp.ithome.com.tw/upload/images/20180928/20082456HtDox7HJjh.jpg

沒有函數呢~要用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

我回應的那串,再加個

MAX(tag2)  over (PARTITION BY tag1) tag13

就做得到了:
https://ithelp.ithome.com.tw/upload/images/20180928/20111566WSuWtlyvBC.png

謝謝各位大大熱心幫助,我已經改成ORACLE可用的語法了

謝謝各位大大熱心幫助,我已經改成ORACLE可用的語法了

0
souda
iT邦研究生 4 級 ‧ 2018-09-27 16:44:17

請搭配程式來做吧!

這要套在模版上 tiptop沒辦法改了...

0
pcw
iT邦研究生 3 級 ‧ 2018-09-27 23:26:46

我用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

dbfiddle有推導過程

感謝幫助

0
張小馬~
iT邦新手 4 級 ‧ 2018-09-28 09:33:41

應該類似這樣的結果吧?
https://ithelp.ithome.com.tw/upload/images/20180928/20111566tXezLKivcw.png

為方便說明,我拆成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的處理,你再酌情修掉就好。

謝謝
說明很清楚,筆記一下
希望未來有機會會用到

我要發表回答

立即登入回答