iT邦幫忙

1

誠心請教一個SQL查詢問題

請教有沒有方法能將下列資料表查詢成如圖結果

https://ithelp.ithome.com.tw/upload/images/20191129/20123241qasboHop8P.jpg

#2019/12/02
謝謝各位大大,十分抱歉我表達的不清楚
以下為table
id date loop value
1 2019-12-2 01:00:00 1 1
2 2019-12-2 02:00:00 1 2
3 2019-12-2 03:00:00 1 3
4 2019-12-2 01:00:00 2 4
5 2019-12-2 02:00:00 2 5
6 2019-12-2 03:00:00 2 6
7 2019-12-2 01:00:00 3 7
8 2019-12-2 02:00:00 3 8
9 2019-12-2 03:00:00 3 9
10 2019-12-2 01:00:00 4 1
11 2019-12-2 02:00:00 4 2
12 2019-12-2 03:00:00 4 3
13 2019-12-2 01:00:00 5 4
14 2019-12-2 02:00:00 5 5
15 2019-12-2 03:00:00 5 6
16 2019-12-2 04:00:00 1 11

--目前參考先進的解答後,修改為如下--
with TEMP AS ( SELECT date, loop, value FROM va ) SELECT
date,
CASE
WHEN
loop= 1 THEN value
END loop1,
CASE
WHEN
loop= 2 THEN value
END loop2,
CASE
WHEN
loop= 3 THEN value
END loop3,
CASE
WHEN
loop= 4 THEN value
END loop4,
CASE
WHEN
loop= 5 THEN value
END loop5
FROM
TEMP
ORDER BY loop

目前結果如下
https://ithelp.ithome.com.tw/upload/images/20191202/20123241FFzs0m5JWV.png

但我想查詢結果如下
https://ithelp.ithome.com.tw/upload/images/20191202/20123241SKCIs6K9sV.png

煩請各位先進幫幫忙,萬分感謝

forumcr iT邦新手 5 級 ‧ 2019-12-02 16:50:51 檢舉
抱歉,我是使用sqlite想去解決這個問題,多謝各位先進們提供的方法,我會去實作看看,有問題再跟各位請教,感謝
8
一級屠豬士
iT邦高手 1 級 ‧ 2019-11-29 22:41:10
最佳解答
-- 使用 Postgresql

create table ithelp191129 (
  id smallint not null primary key
, yamaloop smallint not null
, val smallint not null
);

insert into ithelp191129 values
(1,1,12),(2,1,13),(3,1,4),
(4,2,11),(5,2,2),(6,2,5),
(8,3,12),(9,3,34),(10,3,56),
(11,4,78),(12,4,54),(13,4,23),
(14,5,76),(15,5,13),(16,5,5),
(17,6,4),(18,6,7),(19,6,8);

with t1 as (
select case row_number() over (order by id) % 3
        when 1 then 1
        when 2 then 2
        else 3
       end as grp
     , yamaloop
     , val
  from ithelp191129
), t2 as (
select grp
     , sum(val) filter (where yamaloop = 1) as loop1
     , sum(val) filter (where yamaloop = 2) as loop2
     , sum(val) filter (where yamaloop = 3) as loop3
     , sum(val) filter (where yamaloop = 4) as loop4
     , sum(val) filter (where yamaloop = 5) as loop5
     , sum(val) filter (where yamaloop = 6) as loop6
  from t1
 group by grp
)
select loop1, loop2, loop3, loop4, loop5, loop6
  from t2
 order by grp
;

+-------+-------+-------+-------+-------+-------+
| loop1 | loop2 | loop3 | loop4 | loop5 | loop6 |
+-------+-------+-------+-------+-------+-------+
|    12 |    11 |    12 |    78 |    76 |     4 |
|    13 |     2 |    34 |    54 |    13 |     7 |
|     4 |     5 |    56 |    23 |     5 |     8 |
+-------+-------+-------+-------+-------+-------+
(3 rows)

最麻煩的是在輸入資料, insert into 那裡.
雖然標題有個誠心,但是只貼照片,還要老眼昏花的看著照片,慢慢打資料...

-- SQLite 版

create table it191202 (
  id int not null primary key
, date date not null
, loop int not null
, value int not null
);

insert into it191202 values
(1, '2019-12-2 01:00:00', 1, 1),
(2, '2019-12-2 02:00:00', 1, 2),
(3, '2019-12-2 03:00:00', 1, 3),
(4, '2019-12-2 01:00:00', 2, 4),
(5, '2019-12-2 02:00:00', 2, 5),
(6, '2019-12-2 03:00:00', 2, 6),
(7, '2019-12-2 01:00:00', 3, 7),
(8, '2019-12-2 02:00:00', 3, 8),
(9, '2019-12-2 03:00:00', 3, 9),
(10, '2019-12-2 01:00:00', 4, 1),
(11, '2019-12-2 02:00:00', 4, 2),
(12, '2019-12-2 03:00:00', 4, 3),
(13, '2019-12-2 01:00:00', 5, 4),
(14, '2019-12-2 02:00:00', 5, 5),
(15, '2019-12-2 03:00:00', 5, 6),
(16, '2019-12-2 04:00:00', 1, 11);

--

select date
     , sum(case when loop = 1
           then value
           end) loop1
     , sum(case when loop = 2
           then value
           end) loop2
     , sum(case when loop = 3
           then value
           end) loop3
     , sum(case when loop = 4
           then value
           end) loop4
     , sum(case when loop = 5
           then value
           end) loop5
  from it191202
 group by date
 order by date;

date                loop1       loop2       loop3       loop4       loop5     
------------------  ----------  ----------  ----------  ----------  ----------
2019-12-2 01:00:00  1           4           7           1           4         
2019-12-2 02:00:00  2           5           8           2           5         
2019-12-2 03:00:00  3           6           9           3           6         
2019-12-2 04:00:00  11                                                        


看更多先前的回應...收起先前的回應...
ckp6250 iT邦新手 3 級 ‧ 2019-11-30 06:01:27 檢舉

mysql 似乎沒有 row_number() 可用,比較麻煩一點。
經由您的提示,Postgresql 我花了些時間研習了一陣子,收獲良多。

答案漂亮正確
可惜百密一疏

一般會只說sql而不特別指明是那一家資料庫的新手
通常都以為這世上只有 MS SQL Server=sql
/images/emoticon/emoticon13.gif

MySQL 8 有 row_number(). 之前的版本,也可以另外產生,這個之前我已經寫了不少範例.

那個把SQL 跟 rdbms 分不清的,這是個自由的國度.也只能盡量講一下.

ckp6250 iT邦新手 3 級 ‧ 2019-12-02 19:19:49 檢舉

嗯,我是用 mariadb,剛查了一下,10.2版開始有提供,

感恩指導。

forumcr iT邦新手 5 級 ‧ 2019-12-03 11:02:40 檢舉

太厲害了,感謝您~~

2
ckp6250
iT邦新手 3 級 ‧ 2019-11-29 20:11:14
with temp As (
	if(mod(id,3)=0,3,mod(id,3)) AS id,
	select loop,	
	value
	from xxx ;	
) select
	sum(if(loop=1,value,0)) AS loop1,
	sum(if(loop=2,value,0)) AS loop2,
	sum(if(loop=3,value,0)) AS loop3,
	sum(if(loop=4,value,0)) AS loop4,
	sum(if(loop=5,value,0)) AS loop5,
	sum(if(loop=6,value,0)) AS loop6
	from temp
	group by id;

但因為 id 漏掉了一個 7 ,做出來可能會有問題,不過原理就是如此,請自行變化。

小魚 iT邦大師 1 級 ‧ 2019-11-29 20:27:13 檢舉

這限制太大了吧.
另外你應該要說你用的是哪種SQL,
不同SQL語法會不一樣.

ckp6250 iT邦新手 3 級 ‧ 2019-11-29 21:04:40 檢舉

語法可能不太一樣,但原理應該差不多。

1
純真的人
iT邦高手 1 級 ‧ 2019-11-30 00:30:34

可以用PIVOT 扭轉資料...

declare @a table(
	id int
	,[loop] int
	,[value] int
)

insert into @a
values(1,1,12),(2,1,13),(3,1,4),
(4,2,11),(5,2,2),(6,2,5),
(8,3,12),(9,3,34),(10,3,56),
(11,4,78),(12,4,54),(13,4,23),
(14,5,76),(15,5,13),(16,5,5),
(17,6,4),(18,6,7),(19,6,8);

select isNull([1],0) [loop1] 
,isNull([2],0) [loop2] 
,isNull([3],0) [loop3] 
,isNull([4],0) [loop4] 
,isNull([5],0) [loop5] 
,isNull([6],0) [loop6] 
from (
	select Row_Number()Over(partition by [loop] order by [loop]) Sort
	,[loop]
	,[value]
	from @a
) k
pivot(
	max([value])
	for [loop] in([1],[2],[3],[4],[5],[6])
) p

https://ithelp.ithome.com.tw/upload/images/20191130/200613690L0Ey5hAxa.png

forumcr iT邦新手 5 級 ‧ 2019-12-02 17:32:47 檢舉

抱歉,我是使用sqlite想去解決這個問題,但sqlite似乎沒有pivot,感謝您的協助

3
阿展展展
iT邦好手 1 級 ‧ 2019-11-30 04:24:12

阿你是要用什麼sql寫0.0

0
rexsh
iT邦新手 5 級 ‧ 2019-12-07 13:49:06

不知道你的資料量多大,倘若不考慮效能,此未使用函數的sql在 sqlite應該是可以執行的
若資料量很大,可加上L0.[date]的時間範圍
參考看看

	SELECT  L0.[date], 
	( SELECT   L1.[value] FROM [APPLE].[dbo].[looptable] L1 where L1.loop=1 and L1.[date]=L0.date) loop1,
	( SELECT   L2.[value] FROM [APPLE].[dbo].[looptable] L2 where L2.loop=2 and L2.[date]=L0.date) loop2,
	( SELECT   L3.[value] FROM [APPLE].[dbo].[looptable] L3 where L3.loop=3 and L3.[date]=L0.date) loop3,
	( SELECT   L4.[value] FROM [APPLE].[dbo].[looptable] L4 where L4.loop=4 and L4.[date]=L0.date) loop4,
	( SELECT   L5.[value] FROM [APPLE].[dbo].[looptable] L5 where L5.loop=5 and L5.[date]=L0.date) loop5
  FROM [APPLE].[dbo].[looptable] L0 GROUP BY L0.[date] ORDER BY L0.[date]

    

https://ithelp.ithome.com.tw/upload/images/20191207/20110858cCDZSpSy1U.png

forumcr iT邦新手 5 級 ‧ 2019-12-11 23:06:37 檢舉

謝謝,我試試看

我要發表回答

立即登入回答