T-SQL 多筆金額統計顯示方法

sql 資料庫

匿名 2016-02-01 16:35:24 ‧ 3070 瀏覽

分享至

請問各位大大假如我目前T-SQL語法如下
但顯示出結果如圖上表格，同一間廠商會有相同的總金額顯示
該如何修改使其結果如圖下表格，同一間廠商只顯示第一筆總金額，後面空白顯示，謝謝

SELECT 廠商 ,
       品號 ,
       金額 ,
       (SELECT SUM(S.金額)
        FROM S.資料表
        WHERE S.品號 = 品號
        GROUP BY S.廠商) '總金額'
FROM 資料表

登入發表討論

直播研討會

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

直播中

1 個回答

純真的人

iT邦大師 1 級 ‧ 2016-02-01 17:45:02

最佳解答

try

&lt;pre class="c" name="code">
select [廠商],[品號],[金額]
,(case when [群組排序] &lt;> 1 then '' else Convert(varchar,[總金額]) end) as [總金額]
from (
	select Row_Number() Over(partition by [廠商] order by [廠商]) as [群組排序]
	,*
	from ((
		select N'A' as [廠商]
		, '123' as [品號]
		, 100 as [金額]
		, 100 as [總金額]
	)union(
		select N'A' as [廠商]
		, '456' as [品號]
		, 200 as [金額]
		, 300 as [總金額]
	)union(
		select N'B' as [廠商]
		, '789' as [品號]
		, 300 as [金額]
		, 300 as [總金額]
	)) as k
) as k