如何先显示明细，后跟着显示汇总（分组）

[ye]

table:

username shuliang date
张三 10 2009-1-1
李四 5 2009-1-1
张三 20 2009-1-2
李四 3 2009-1-2


sql语句实现


张三 10 2009-1-1
张三 20 2009-1-2
张三（合计）30
李四 3 2009-1-2
李四 5 2009-1-1
李四（合计）8

[dawugui]

select username , shuliang , convert(varchar(10),date,120) date from tb
union all
select username + '(合计)' username, sum(shuliang) shuliang  , date = '' from tb group by username + '(合计)'
order by username

[dawugui]

create table tb(username varchar(10), shuliang int, date datetime)
insert into tb values('张三' ,     10 ,     '2009-1-1') 
insert into tb values('李四' ,     5  ,     '2009-1-1') 
insert into tb values('张三' ,     20 ,     '2009-1-2') 
insert into tb values('李四' ,     3  ,     '2009-1-2')
go

select username , shuliang , convert(varchar(10),date,120) date from tb
union all
select username + '(合计)' username, sum(shuliang) shuliang  , date = '' from tb group by username + '(合计)'
order by username

drop table tb 

/*
username         shuliang    date       
---------------- ----------- ---------- 
李四               5           2009-01-01
李四               3           2009-01-02
李四(合计)           8           
张三               20          2009-01-02
张三               10          2009-01-01
张三(合计)           30          

（所影响的行数为 6 行）

*/

[ks_reny]

select *  from
(select * from table1 
union
select username+'(合计)'，sum(shuliang),'' from
group by username) a
order by username

[htl258_Tony]

declare @t table(username varchar(10), shuliang int, date varchar(10))
insert @t select '张三',10,'2009-1-1'
insert @t select '李四',5,'2009-1-1'
insert @t select '张三',20,'2009-1-2'
insert @t select '李四',3,'2009-1-2'

select 
    username=case when grouping(username)=1 then '总计' when grouping(date)=1 then username+'小计' else username end ,
    sum(shuliang) shuliang,
    isnull(date,'') date
from @t
group by username,date
with rollup

username       shuliang    date
-------------- ----------- ----------
李四             5           2009-1-1
李四             3           2009-1-2
李四小计           8           
张三             10          2009-1-1
张三             20          2009-1-2
张三小计           30          
总计             38          

(7 行受影响)

[-狙击手-]

with rollup

[loveeqing]

up

[zhongasen]

有一個compute 就可以呀！

[you_tube]

declare @t table
    (
      username varchar(10),
      shuliang int,
      date varchar(10)
    )
insert  @t select  '张三', 10, '2009-1-1'
insert  @t select  '李四', 5, '2009-1-1'
insert  @t select  '张三', 20, '2009-1-2'
insert  @t select  '李四', 3, '2009-1-2'

SELECT  username = CASE WHEN GROUPING(username) = 1 THEN '总计'
                        WHEN GROUPING(date) = 1 THEN username + '小计'
                        ELSE username
                   END,
        SUM(shuliang) shuliang,
        ISNULL(date, '') date
FROM    @t
group by username,
        date
        with rollup 

username       shuliang    date
-------------- ----------- ----------
李四             5           2009-1-1
李四             3           2009-1-2
李四小计           8           
张三             10          2009-1-1
张三             20          2009-1-2
张三小计           30          
总计             38