SQL 語法一問 (MSSQL)

#mssql #sql

緯大啊緯大人 2021-11-05 09:41:19 ‧ 1434 瀏覽

分享至

先放上寫好的 Demo

好久沒來問問題~有個SQL語法需求~有兩張表內容如下

表B

表F

表F的BId對應表B的Id，做join後如下

select * from B
inner join F on B.Id = F.BId

需求來了，我想要在同一列，顯示一間公司的每年狀態+某年某季的Count，不知道有什麼方法可以更好處理這種問題! 我自己有硬兜出以下語法，是可以呈現我要的結果，但我知道很爛且效能很差，應該需要新的知識補充加強，感謝各位

我的語法如下：

;with T1 as
(
select B.*,F.Season,F.Count  from B
inner join F on B.Id = F.BId
)

select  distinct
Name,
(select top 1 Status from T1 as T2 where T1.Name = T2.Name and Year=109 ) as [109_Status],
(select top 1 Status from T1 as T2 where T1.Name = T2.Name and Year=110 ) as [110_Status],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=109 and Season=1 ) as [109_s1_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=109 and Season=2 ) as [109_s2_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=109 and Season=3 ) as [109_s3_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=110 and Season=1 ) as [110_s1_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=110 and Season=2 ) as [110_s2_count],
(select top 1 Count from T1 as T2 where T1.Name = T2.Name and Year=110 and Season=3 ) as [110_s3_count]
from T1

我想要呈現的結果如下：

看更多先前的討論...收起先前的討論...

一級屠豬士 iT邦大師 1 級 ‧ 2021-11-05 10:45:04 檢舉

table 設計不太好. 一年四季是固定的,既然你要用季的方式來存放及顯示,你再思考看看,做一下調整,一步步來也不用急著想要一步到位.

緯大啊緯大人 iT邦好手 1 級 ‧ 2021-11-05 10:53:28 檢舉

對，這和一般設計不大一樣，是因為需求造成表必須生成這樣，且裡面還有更複雜的事要處理~我是簡化過了QQ

不過我的問題主要是在於，類似這樣的join表單，如何將所有資料這樣依條件取出且只產出在同一列上!

一級屠豬士 iT邦大師 1 級 ‧ 2021-11-05 13:06:26 檢舉

輔助表
https://ithelp.ithome.com.tw/questions/10197476
https://ithelp.ithome.com.tw/articles/10137154
以前也有類似的問題討論,上面有兩個,你也可以再找看看.

緯大啊緯大人 iT邦好手 1 級 ‧ 2021-11-05 14:06:17 檢舉

哦，您用一個假表去相乘做出對應欄位，也是個方法!! 感謝~~~

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

石頭

iT邦高手 1 級 ‧ 2021-11-05 10:35:22

最佳解答

你可以嘗試使用 MAX with GROUP BY

;with T1 as
(
select B.*,F.Season,F.Count  from B
inner join F on B.Id = F.BId
)
SELECT Name,
       MAX(CASE WHEN Year = 109 THEN Status END),
       MAX(CASE WHEN Year = 110 THEN Status END),
       MAX(CASE WHEN Year = 109 AND Season = 1 THEN [COUNT] END),
       MAX(CASE WHEN Year = 109 AND Season = 2 THEN [COUNT] END),
       MAX(CASE WHEN Year = 109 AND Season = 3 THEN [COUNT] END),
       MAX(CASE WHEN Year = 110 AND Season = 1 THEN [COUNT] END),
       MAX(CASE WHEN Year = 110 AND Season = 2 THEN [COUNT] END),
       MAX(CASE WHEN Year = 110 AND Season = 3 THEN [COUNT] END)
FROM T1
GROUP BY Name

sqlfiddle