大家帮忙好急................
有一表A
type id name price amount money
A 1 SS 1.2 2 2.4
A 3 BB 3 2 6
B 4 YY 9 1 9
C 6 oo 3 3 9
要得到
type name price amount money
SS 1.2 2 2.4
BB 3 2 6
A合计 8.4
YY 9 1 9
B合计 9
oo 3 3 9
C合计 9
总合计 26.4
[解决办法]
Create Table A
(typeVarchar(10),
idInt,
nameVarchar(10),
priceNumeric(10, 1),
amount Int,
[money]Numeric(10, 1))
Insert A Select 'A ', 1, 'SS ', 1.2, 2, 2.4
Union All Select 'A ', 3, 'BB ', 3, 2, 6
Union All Select 'B ', 4, 'YY ', 9, 1, 9
Union All Select 'C ', 6, 'oo ', 3, 3, 9
GO
Select
(Case When Grouping(type) = 1 Then N '总合计 ' Else Case When Grouping(name) = 1 Then type + N '合计 ' Else ' ' End End) As type,
name,
price,
amount,
SUM([money]) As [money]
From
A
Group By
type,
name,
price,
amount
With Rollup
Having
Grouping([amount])=0 Or Grouping(name)=1
GO
Drop Table A
--Result
/*
typenamepriceamountmoney
BB3.026.0
SS1.222.4
A合计NULLNULLNULL8.4
YY9.019.0
B合计NULLNULLNULL9.0
oo3.039.0
C合计NULLNULLNULL9.0
总合计NULLNULLNULL26.4
*/
[解决办法]
create table T(type char(1), id int, name varchar(20), price decimal(10, 1), amount int, [money] decimal(10, 1))
insert T select 'A ', 1, 'SS ', 1.2, 2, 2.4
union all select 'A ', 3, 'BB ', 3, 2, 6
union all select 'B ', 4, 'YY ', 9, 1, 9
union all select 'C ', 6, 'oo ', 3, 3, 9
select type, name, price, amount, [money] from
(
select type= ' ', name, cast(price as varchar) as price, cast(amount as varchar) as amount, [money],
s1=0, s2=type, s3=0
from T
union all
select type+ '合计 ', ' ', ' ', ' ', sum([money]),
s1=0, s2=type, s3=1
from T
group by type
union all
select type= '总合计 ', ' ', ' ', ' ', sum([money]),
s1=1, s= ' ', s3=1
from T
)tmp order by s1, s2, s3
--result
type name price amount money
------ -------------------- ------------------------------ ------------------------------ ----------------------------------------
SS 1.2 2 2.4
BB 3.0 2 6.0
A合计 8.4
YY 9.0 1 9.0
B合计 9.0
oo 3.0 3 9.0
C合计 9.0
总合计 26.4
(8 row(s) affected)
