請教有沒有方法能將下列資料表查詢成如圖結果
#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
目前結果如下
但我想查詢結果如下
煩請各位先進幫幫忙,萬分感謝
-- 使用 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
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 ,做出來可能會有問題,不過原理就是如此,請自行變化。
可以用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
不知道你的資料量多大,倘若不考慮效能,此未使用函數的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]