iT邦幫忙

0

SQL 語法一問 (MSSQL)

先放上寫好的 Demo

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

表B

https://ithelp.ithome.com.tw/upload/images/20211105/20135501ifhqywAYj4.png

表F

https://ithelp.ithome.com.tw/upload/images/20211105/2013550193rj9weT4X.png

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

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

https://ithelp.ithome.com.tw/upload/images/20211105/20135501GCBqHWVOu9.png

需求來了,我想要在同一列,顯示一間公司的每年狀態+某年某季的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

我想要呈現的結果如下:

https://ithelp.ithome.com.tw/upload/images/20211105/20135501y22Md3FmkC.png

看更多先前的討論...收起先前的討論...
table 設計不太好. 一年四季是固定的,既然你要用季的方式來存放及顯示,你再思考看看,做一下調整,一步步來也不用急著想要一步到位.
對,這和一般設計不大一樣,是因為需求造成表必須生成這樣,且裡面還有更複雜的事要處理~我是簡化過了QQ

不過我的問題主要是在於,類似這樣的join表單,如何將所有資料這樣依條件取出且只產出在同一列上!
輔助表
https://ithelp.ithome.com.tw/questions/10197476
https://ithelp.ithome.com.tw/articles/10137154
以前也有類似的問題討論,上面有兩個,你也可以再找看看.
哦,您用一個假表去相乘做出對應欄位,也是個方法!! 感謝~~~
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

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

wow,這也是一個很酷的作法,且效能好多了~~感謝

有一個小問題,Status假設不是數字,就不能MAX了 XD

石頭 iT邦高手 1 級 ‧ 2021-11-05 12:03:55 檢舉

MAX 就算不是數字也可以用 因為我是使用 condition aggregate function 來處理

我要發表回答

立即登入回答