表a里的内容如下:
id s1 s2 s3
1 10 15 22
2 22 20 15
3 12 25 33要求显示的结果这样:
id s1 s2 s3
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
44 60 70
请问各位用SQL怎么实现啊
id s1 s2 s3
1 10 15 22
2 22 20 15
3 12 25 33要求显示的结果这样:
id s1 s2 s3
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
44 60 70
请问各位用SQL怎么实现啊
alter table a add column tt as (s1+s2+s3)
id,
SUM(s1) As s1,
SUM(s2) As s2,
SUM(s3) As s3,
SUM(s1 + s2 + s3) As s
From
TEST
Group By
id
With Rollup
(id Int,
s1 Int,
s2 Int,
s3 Int)
Insert TEST Select 1, 10, 15, 22
Union All Select 2, 22, 20, 15
Union All Select 3, 12, 25, 33
GO
Select
id,
SUM(s1) As s1,
SUM(s2) As s2,
SUM(s3) As s3,
SUM(s1 + s2 + s3) As s
From
TEST
Group By
id
With Rollup
GO
Drop Table TEST
--Result
/*
id s1 s2 s3 s
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
NULL 44 60 70 174
*/
insert @a select 1, 10 ,15 ,22
union all select 2 ,22 ,20 ,15
union all select 3, 12 ,25, 33select id,s1,s2,s3,s4=s1+s2+s3 from @a
union all
select id=(select max(id) from @a),sum(s1)+1,sum(s2),sum(s3),sum(s1+s2+s3) from @a
insert ta select 1, 10, 15, 22
insert ta select 2, 22, 20, 15
insert ta select 3, 12, 25, 33select id=rtrim(id),s1,s2,s3,
(select rtrim(sum(s1))from (select s1 union select s2 union all select s3)t)
from ta
union all
select '',sum(s1),sum(s2),sum(s3),''from ta
id s1 s2 s3
------------ ----------- ----------- ----------- ------------
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
44 60 70 (所影响的行数为 4 行)
id s1 s2 s3
1 10 15 22
2 22 20 15
3 12 25 33要求显示的结果这样:
id s1 s2 s3
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
44 60 70
select id,s1,s2,s3,s1+s2+s3 s4 from tb
union all
select id = null,sum(s1) s1 , sum(s2) s2 , sum(s3) s3 , s4 = null from tb
insert into @a select 1,10,15,22
union all select 2,22,20,15
union all select 3,12,25,33
select id,s1,s2,s3,s1+s2+s3 from @a union all select null,sum(s1),sum(s2),sum(s3),null from @a
insert ta select 1, 10, 15, 22
insert ta select 2, 22, 20, 15
insert ta select 3, 12, 25, 33select id=rtrim(id),s1,s2,s3,rtrim(s1+s2+s3)
from ta
union all
select '',sum(s1),sum(s2),sum(s3),''from taid s1 s2 s3
------------ ----------- ----------- ----------- ------------
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
44 60 70 (所影响的行数为 4 行)
declare @tab table([id] int,s1 int,s2 int,s3 int)
insert @tab
select 1,10,15,22
union all
select 2,22,20,15
union all
select 3,12,25,33Select *,(s1 + s2 + s3) As s
from @tab
union all
select null,sum(s1),sum(s2),sum(s3),null
from @tab
(所影响的行数为 3 行)id s1 s2 s3 s
----------- ----------- ----------- ----------- -----------
1 10 15 22 47
2 22 20 15 57
3 12 25 33 70
NULL 44 60 70 NULL(所影响的行数为 4 行)
alter table a add column tt as (s1+s2+s3)
--用下面的查詢
Select
id,
SUM(s1) As s1,
SUM(s2) As s2,
SUM(s3) As s3,
SUM(tt) As s
From
TEST
Group By
id
With Rollup