SELECT jsdw , b , c FROM tab ORDER BY jsdw COMPUTE SUM(b) ,sum(c)BY jsdw COMPUTE SUM(b),sum(c)
select jsdw,b,c from table order by jsdw compute sum(b),sum(c) by jsdw 只有一点需要注意:使用文本显示结果才好看
declare @ table(jsdw varchar(10),b int,c int) insert into @ select 'aa', 1 , 2 union all select 'aa' , 1 , 5 union all select 'aa' , 4 , 7 union all select 'bb' , 3 , 2 union all select 'bb' , 1 , 4 union all select 'bb' , 3 , 7 union all select 'cc' , 8 , 2 union all select 'cc' , 2 , 2 union all select 'cc' , 2, 7SELECT jsdw , b , c FROM @ ORDER BY jsdw COMPUTE SUM(b) ,sum(c)BY jsdw COMPUTE SUM(b),sum(c)--测试结果(9 row(s) affected)jsdw b c ---------- ----------- ----------- aa 1 2 aa 1 5 aa 4 7 sum =========== 6 sum =========== 14 jsdw b c ---------- ----------- ----------- bb 3 2 bb 1 4 bb 3 7 sum =========== 7 sum =========== 13 jsdw b c ---------- ----------- ----------- cc 8 2 cc 2 2 cc 2 7 sum =========== 12 sum =========== 11 sum =========== 25 sum =========== 38 (13 row(s) affected)
如果是要生成报表,直接在报表里进行分组汇总。不要在SQL查询中作这样子的处理。
SELECT jsdw , b , c FROM tab ORDER BY jsdw COMPUTE SUM(b) ,sum(c)BY jsdw 连这个基本的语句也不会昂回去好好学学数据库语句吧^
declare @table table (jsdw char(2),b int,c int) insert @table select 'aa', 1, 2 union all select 'aa', 1, 5 union all select 'aa', 4, 7 union all select 'bb', 3, 2 union all select 'bb', 1, 4 union all select 'bb', 3, 7 union all select 'cc', 8, 2 union all select 'cc', 2, 2 union all select 'cc', 2, 7select jsdw,b,c from ( select * from( select jsdw,b,c,0 flag from @table union all select jsdw+'小计',sum(b),sum(c),1 from @table group by jsdw) a union all select '合计',sum(b),sum(c),2 from @table ) a order by jsdw ,flag/* jsdw b c ------ ----------- ----------- aa 1 2 aa 1 5 aa 4 7 aa小计 6 14 bb 3 2 bb 1 4 bb 3 7 bb小计 7 13 cc 8 2 cc 2 2 cc 2 7 cc小计 12 11 合计 25 38 */
FROM tab
ORDER BY jsdw
COMPUTE SUM(b) ,sum(c)BY jsdw
COMPUTE SUM(b),sum(c)
from table
order by jsdw
compute sum(b),sum(c) by jsdw
只有一点需要注意:使用文本显示结果才好看
insert into @ select
'aa', 1 , 2 union all select
'aa' , 1 , 5 union all select
'aa' , 4 , 7 union all select
'bb' , 3 , 2 union all select
'bb' , 1 , 4 union all select
'bb' , 3 , 7 union all select
'cc' , 8 , 2 union all select
'cc' , 2 , 2 union all select
'cc' , 2, 7SELECT jsdw , b , c
FROM @
ORDER BY jsdw
COMPUTE SUM(b) ,sum(c)BY jsdw
COMPUTE SUM(b),sum(c)--测试结果(9 row(s) affected)jsdw b c
---------- ----------- -----------
aa 1 2
aa 1 5
aa 4 7 sum
===========
6 sum
===========
14
jsdw b c
---------- ----------- -----------
bb 3 2
bb 1 4
bb 3 7 sum
===========
7 sum
===========
13
jsdw b c
---------- ----------- -----------
cc 8 2
cc 2 2
cc 2 7 sum
===========
12 sum
===========
11
sum
===========
25 sum
===========
38
(13 row(s) affected)
FROM tab
ORDER BY jsdw
COMPUTE SUM(b) ,sum(c)BY jsdw
连这个基本的语句也不会昂回去好好学学数据库语句吧^
(jsdw char(2),b int,c int)
insert @table
select
'aa', 1, 2
union all select
'aa', 1, 5
union all select
'aa', 4, 7
union all select
'bb', 3, 2
union all select
'bb', 1, 4
union all select
'bb', 3, 7
union all select
'cc', 8, 2
union all select
'cc', 2, 2
union all select
'cc', 2, 7select jsdw,b,c
from (
select * from( select jsdw,b,c,0 flag from @table
union all
select jsdw+'小计',sum(b),sum(c),1 from @table
group by jsdw) a
union all
select '合计',sum(b),sum(c),2 from @table
) a
order by jsdw ,flag/*
jsdw b c
------ ----------- -----------
aa 1 2
aa 1 5
aa 4 7
aa小计 6 14
bb 3 2
bb 1 4
bb 3 7
bb小计 7 13
cc 8 2
cc 2 2
cc 2 7
cc小计 12 11
合计 25 38
*/
不过我要对小电脑说句话:我出来工作的时候,你可能还没有出世了。我是跟比尔同一年代的,