我在 SQL Server 中有如下表，使用以下代碼：
select col1, count(*) as col2,
case when col1 = 'aaa' then 'xxx'
when col1 = 'bbb' then 'yyy'
when col1 = 'ccc' then 'zzz'
else 'ttt'
end 'col3'
from table1
group by col1
col1 | col2 | col3
----------------------
aaa | 10 | xxx
bbb | 20 | yyy
ccc | 30 | yyy
如何在 SQL Server 中修改我的查詢，以便在 col2 中添加具有值總和的新行？所以我需要像下面這樣的東西：
col1 | col2 | col3
----------------------
aaa | 10 | xxx
bbb | 20 | yyy
ccc | 30 | yyy
sum | 60 | sum of values in col2
uj5u.com熱心網友回復：
您可以為此使用 ROLLUP。檔案解釋了這是如何作業的。https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15
select col1, count(*) as col2,
case when col1 = 'aaa' then 'xxx'
when col1 = 'bbb' then 'yyy'
when col1 = 'ccc' then 'zzz'
else 'ttt'
end 'col3'
from table1
group by rollup(col1)
---編輯--- 這是更新的代碼，演示了coalesce 的作業原理。
select coalesce(col1, 'sum')
, count(*) as col2
, case when col1 = 'aaa' then 'xxx'
when col1 = 'bbb' then 'yyy'
when col1 = 'ccc' then 'zzz'
else 'ttt'
end 'col3'
from table1
group by rollup(col1)
uj5u.com熱心網友回復：
我傾向于喜歡GROUPING SETS這樣的專案
Declare @YourTable Table ([col1] varchar(50),[col2] int,[col3] varchar(50)) Insert Into @YourTable Values
('aaa',10,'xxx')
,('bbb',20,'yyy')
,('ccc',30,'yyy')
Select col1 = coalesce(col1,'sum')
,col2 = sum(Col2)
,col3 = coalesce(col3,'sum of values in col2')
from @YourTable
Group by grouping sets ( (col1,col3)
,()
)
結果
col1 col2 col3
aaa 10 xxx
bbb 20 yyy
ccc 30 yyy
sum 60 sum of values in col2
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/357316.html
標籤：sql sql-server 查询语句
下一篇：以特定方式對列值進行排序