iT邦幫忙

1

SQL考題疑問?

https://ithelp.ithome.com.tw/upload/images/20210422/20136784rrJTJTGeOv.png
大家好,請問有誰會解這一題的SQL,我想了好久還是想不出來

看更多先前的討論...收起先前的討論...
練習SQL...就是去解別人的問題~上網Google找一小段解法~再把各小段解法組合起來~就是答出問題的方式囉@@"
m10423010 iT邦新手 5 級 ‧ 2021-04-22 23:54:10 檢舉
謝謝大大的建議,我會努力的
石頭 iT邦研究生 2 級 ‧ 2021-04-23 09:00:55 檢舉
這題Keywork: Unpivot 有蠻多方法可以實現Unpivot 可以上網google看看
君莫笑 iT邦新手 5 級 ‧ 2021-04-23 16:21:07 檢舉
另一種是用這種小技巧每筆Row當中再去split
T-SQL筆記12_如何透過for xml path 搭配STUFF將多ROW資料合併同一ROW
https://coolmandiary.blogspot.com/2020/11/t-sql12for-xml-path-stuffrowrow.html
2
rogeryao
iT邦大師 3 級 ‧ 2021-04-22 22:16:05
最佳解答
CREATE TABLE A (
XYEAR INT, 
Q1 INT,
Q2 INT,
Q3 INT,
Q4 INT);

INSERT INTO A
VALUES
(2017,200,250,235,266),
(2018,195,220,215,240);
SELECT XYEAR,'Q1' AS 'QQ',MAX(Q1) AS 'AMOUNT'
FROM A
GROUP BY XYEAR
UNION 
SELECT XYEAR,'Q2' AS 'QQ',MAX(Q2) AS 'AMOUNT'
FROM A
GROUP BY XYEAR
UNION
SELECT XYEAR,'Q3' AS 'QQ',MAX(Q3) AS 'AMOUNT'
FROM A
GROUP BY XYEAR
UNION
SELECT XYEAR,'Q4' AS 'QQ',MAX(Q4) AS 'AMOUNT'
FROM A
GROUP BY XYEAR
ORDER BY XYEAR,QQ

Demo

看更多先前的回應...收起先前的回應...
m10423010 iT邦新手 5 級 ‧ 2021-04-22 22:35:29 檢舉

謝謝您的回覆,請問一下,如果是顛倒過來由下面的表格轉成上面的表格SQL又該如何下?

rogeryao iT邦大師 3 級 ‧ 2021-04-22 22:55:54 檢舉
CREATE TABLE X (
XYEAR INT, 
QQ VARCHAR(10),
AMOUNT INT);

INSERT INTO X
VALUES
(2017,'Q1',200),
(2017,'Q2',250),
(2017,'Q3',235),
(2017,'Q4',266),
(2018,'Q1',195),
(2018,'Q2',220),
(2018,'Q3',215),
(2018,'Q4',240);
SELECT A.XYEAR,A.AMOUNT AS 'Q1',B.AMOUNT AS 'Q2',C.AMOUNT AS 'Q3',D.AMOUNT AS 'Q4'
FROM X AS A
LEFT JOIN X AS B ON B.XYEAR=A.XYEAR AND B.QQ='Q2'
LEFT JOIN X AS C ON C.XYEAR=A.XYEAR AND C.QQ='Q3'
LEFT JOIN X AS D ON D.XYEAR=A.XYEAR AND D.QQ='Q4'
WHERE A.QQ='Q1' 
ORDER BY A.XYEAR

Demo

m10423010 iT邦新手 5 級 ‧ 2021-04-22 23:02:04 檢舉

可以請教大大是怎麼練習SQL嗎?總覺這種類型的SQL題目都好活,腦袋需要轉一下,不然根本想不出來

rogeryao iT邦大師 3 級 ‧ 2021-04-22 23:10:40 檢舉

怎麼練習SQL ?
上it邦幫忙==>右上方的"放大鏡"查"邦友"==>一級屠豬士,暐翰,純真的人....... 解SQL都很高明.
學SQL沒甚麼絕招,就多看看別人怎麼分析問題,出哪一招.

小魚 iT邦大師 1 級 ‧ 2021-04-22 23:14:56 檢舉

To rogeryao:
你這樣好像會進入到他們的通知裡面 XD

To m10423010:
網路上的SQL語法先學完,
剩下的就是實作經驗了,
學程式需要蠻多實作的.

rogeryao iT邦大師 3 級 ‧ 2021-04-22 23:30:44 檢舉

To 小魚:
沒注意到,已修正了.

哈~難怪為什麼被通知了XD

5
一級屠豬士
iT邦大師 1 級 ‧ 2021-04-22 22:34:03

使用PostgreSQL

create table it0422 (
  xyear int not null
, q1 int not null
, q2 int not null
, q3 int not null
, q4 int not null
);

insert into it0422 values
(2017,200,250,235,266),
(2018,195,220,215,240);

select xyear as "年度"
     , unnest(array['q1', 'q2', 'q3', 'q4']) as "各季"
     , unnest(array[q1, q2, q3, q4]) as "購買金額"
  from it0422
 order by 1,2;

 年度 | 各季 | 購買金額
------+------+----------
 2017 | q1   |      200
 2017 | q2   |      250
 2017 | q3   |      235
 2017 | q4   |      266
 2018 | q1   |      195
 2018 | q2   |      220
 2018 | q3   |      215
 2018 | q4   |      240
(8 rows)

create table it0422x as
select xyear 
     , unnest(array['q1', 'q2', 'q3', 'q4']) as quarter
     , unnest(array[q1, q2, q3, q4]) as qty
  from it0422
 order by 1,2;

select *
  from it0422x;

 xyear | quarter | qty
-------+---------+-----
  2017 | q1      | 200
  2017 | q2      | 250
  2017 | q3      | 235
  2017 | q4      | 266
  2018 | q1      | 195
  2018 | q2      | 220
  2018 | q3      | 215
  2018 | q4      | 240
(8 rows)

select xyear as year
     , min(qty) filter (where quarter = 'q1') as q1
     , min(qty) filter (where quarter = 'q2') as q2
     , min(qty) filter (where quarter = 'q3') as q3
     , min(qty) filter (where quarter = 'q4') as q4
  from it0422x
 group by xyear
 order by xyear;

 year | q1  | q2  | q3  | q4
------+-----+-----+-----+-----
 2017 | 200 | 250 | 235 | 266
 2018 | 195 | 220 | 215 | 240
(2 rows)

-- 使用補助表的方式 
with t1 (quarter, q1, q2, q3, q4) as (
values ('q1', 1, 0, 0, 0)
     , ('q2', 0, 1, 0, 0)
     , ('q3', 0, 0, 1, 0)
     , ('q4', 0, 0, 0, 1)
)
select x.xyear as year
     , sum(x.qty * t1.q1) as q1
     , sum(x.qty * t1.q2) as q2
     , sum(x.qty * t1.q3) as q3
     , sum(x.qty * t1.q4) as q4
  from it0422x x
     , t1
 where x.quarter = t1.quarter
 group by x.xyear
 order by 1;

 year | q1  | q2  | q3  | q4
------+-----+-----+-----+-----
 2017 | 200 | 250 | 235 | 266
 2018 | 195 | 220 | 215 | 240
(2 rows)

-- 使用 case when 方式

select xyear as year
     , min(case 
             when quarter = 'q1' then qty
             else null
           end) as q1
     , min(case 
             when quarter = 'q2' then qty
             else null
           end) as q2
     , min(case 
             when quarter = 'q3' then qty
             else null
           end) as q3
     , min(case 
             when quarter = 'q4' then qty
             else null
           end) as q4
  from it0422x
 group by xyear
 order by xyear;

 year | q1  | q2  | q3  | q4
------+-----+-----+-----+-----
 2017 | 200 | 250 | 235 | 266
 2018 | 195 | 220 | 215 | 240
(2 rows)

-- 大多數 RDBMS 都有 case when , filter 目前是 PostgreSQL 有
-- 可以觀察到, 使用 filter 語法較為簡潔
-- 不管是 補助表, case when , 還是 filter.其實三種方式的原理都是一樣的.

demo

看更多先前的回應...收起先前的回應...
m10423010 iT邦新手 5 級 ‧ 2021-04-22 22:50:03 檢舉

謝謝您的回覆,請問一下,有沒有比較基本的語法,如果是顛倒過來由下面的表格轉成上面的表格SQL又該如何下?
因為我目前還沒學到unnest,還不太會用,我SQL目前只會基本的語法而已

小魚 iT邦大師 1 級 ‧ 2021-04-22 23:12:07 檢舉

你都給自己限制了,
還需要學習嗎?

m10423010 iT邦新手 5 級 ‧ 2021-04-22 23:35:01 檢舉

所謂限制是什麼意思?

我"目前只會" 我"只能" 只這各字就是限制,只可以 只有 只XXXX
還需要講更多嘛 ?

沒想過用filter,謝謝你的範例!

微笑 iT邦新手 4 級 ‧ 2021-04-23 10:41:16 檢舉

with的寫法太帥了吧

jasonOuO iT邦新手 5 級 ‧ 2021-04-27 11:39:36 檢舉

太強,受教了!

0
pilipala
iT邦新手 5 級 ‧ 2021-04-22 23:50:28

MS SQL 的話,可以使用 unpivot 來做到

0
微笑
iT邦新手 4 級 ‧ 2021-04-23 10:37:08

雖然樓上已經有許多解答了,但還是想分享自己練習的結果/images/emoticon/emoticon37.gif

--建表
DECLARE @XP AS TABLE
(
   [年度] varchar(4) NOT NULL,
   [Q1] decimal(11),
   [Q2] decimal(11),
   [Q3] decimal(11),
   [Q4] decimal(11)
)

--放入參考資料
INSERT INTO @XP VALUES('2017',200,250,235,266)
INSERT INTO @XP VALUES('2018',195,220,215,240)

SELECT * FROM @XP

SELECT XP.[年度] 年度,a.各季,a.購買金額
FROM @XP XP
LEFT JOIN ( select [年度] 年度,'Q1' 各季,Q1 購買金額 from @XP ) a ON XP.年度 = a.年度
union
SELECT XP.[年度] 年度,a.各季,a.購買金額
FROM @XP XP
LEFT JOIN ( select [年度] 年度,'Q2' 各季,Q2 購買金額 from @XP ) a ON XP.年度 = a.年度
union
SELECT XP.[年度] 年度,a.各季,a.購買金額
FROM @XP XP
LEFT JOIN ( select [年度] 年度,'Q3' 各季,Q3 購買金額 from @XP ) a ON XP.年度 = a.年度
union
SELECT XP.[年度] 年度,a.各季,a.購買金額
FROM @XP XP
LEFT JOIN ( select [年度] 年度,'Q4' 各季,Q4 購買金額 from @XP ) a ON XP.年度 = a.年度

我要發表回答

立即登入回答