你的type怎么会变了?这是什么规律?
解决方案 »
- SQLServer2000的备份如何还原到SQLServer2005下?
- 菜鸟问题送100分!!!!
- 最近做一个项目,客户已经试用,录入的数据不能有损失,可是每次去都要修改他的数据库结构,这方面的技巧吗?
- 为什么提示变量没声明呢(困难户求助)大神们来捧个场
- 如何写这个sql语句?
- 求一个简单的sql,谢谢
- 请问SQL中如何截取一个varchar的部分字符串?
- 怎样在sql server 2000中复制一张表?
- 很简单的问题
- 备份文件出错,sql server
- 关于sp_add_jobschedule 和 sp_addumpdevice 的问题!
- 菜鸟问题! 下面这条信息是什么意思啊,怎么会这样,
union all
select type+'的汇总:',null,sum(price),type,2 from 表 group by type) aa order by type,flag
union all
select type+'的汇总:',null,sum(price),type,2 from 表 group by type) aa order by type,flag
select cast(id as varchar(100)) id,name,price,type,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end flag1,1 flag2 from 表
union all
select '汇总:',null,sum(price),null,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end,2 from 表
group by case when type='001' then 1 when type in('003','004','002') then 2 else 3 end) aa order by flag1,flag2
insert @ values(1, 'ww' , 20 , '001')
insert @ values(2, 'cc' , 30 , '002')
insert @ values(3 , 'aa' , 22 , '003')
insert @ values(4 , 'bb' , 55 , '002')
insert @ values(5 , 'ff', 55, '008')
insert @ values(6 , 'ee' ,12 , '002')
insert @ values(7 , 'ap', 99 , '001')
insert @ values(8 , 'zz' , 66 , '003')
select * from(
select cast(id as varchar(100)) id,name,price,type,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end flag1,1 flag2 from @
union all
select '汇总:',null,sum(price),null,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end,2 from @
group by case when type='001' then 1 when type in('003','004','002') then 2 else 3 end) aa order by flag1,flag2
create table mytable(id int,name varCHar(20),price numeric(9,2),type varchar(5))
insert into mytable
select 1, 'ww', 20 , '001' union
select 2, 'cc', 30 , '002' union
select 3, 'aa', 22 , '003' union
select 4, 'bb', 55 , '002' union
select 5, 'ff', 55 , '008' union
select 6, 'ee', 12 , '002' union
select 7, 'ap', 99 , '001' union
select 8, 'zz', 66 , '003'
go方法一:group分组合计
select id,case grouping(id) when 0 then type else type+'小计' end,
max(name) as name,sum(price) as price
from mytable
group by type,id with rollup
having grouping(type) = 0
go方法二:union联接
select id,type,name,price
from
(select id,name,price,type,tmporder = type from mytable
union all
select 0,'',sum(price),type+'小计',tmporder = type + '.' from mytable group by type
) a
order by tmporder
from
(select id,name,price,type,tmporder = type+'.' from mytable
union all
select 0,'',sum(price),type+'小计',tmporder = type
from mytable group by type
) a
order by tmporder
select cast(id as varchar(100)) id,name,price,type,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end flag1,1 flag2 from @
union all
select '汇总:',null,sum(price),null,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end,0 from @
group by case when type='001' then 1 when type in('003','004','002') then 2 else 3 end) aa order by flag1,flag2
insert @ values(1, 'ww' , 20 , '001')
insert @ values(2, 'cc' , 30 , '002')
insert @ values(3 , 'aa' , 22 , '003')
insert @ values(4 , 'bb' , 55 , '002')
insert @ values(5 , 'ff', 55, '008')
insert @ values(6 , 'ee' ,12 , '002')
insert @ values(7 , 'ap', 99 , '001')
insert @ values(8 , 'zz' , 66 , '003')
select * from(
select cast(id as varchar(100)) id,name,price,type,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end flag1,1 flag2 from @
union all
select '汇总:',null,sum(price),null,case when type='001' then 1 when type in('003','004','002') then 2 else 3 end,0 from @
group by case when type='001' then 1 when type in('003','004','002') then 2 else 3 end) aa order by flag1,flag2
也就是要如下的汇总行:
ID name price type
1 ww 20 001
2 cc 30 001
3 aa 22 001
null type汇总 72 001
....
null type=001,002,003汇总 72 001
null 其他汇总 72 001
(select id,name,price,type,a= 1
from #punion select 1,'小计',sum(price) price,type,a=2
from #p
group by type) a
order by type,a desc-----
1 小计 119 001
1 ww 20 001
7 ap 99 001
1 小计 97 002
2 cc 30 002
4 bb 55 002
6 ee 12 002
1 小计 88 003
3 aa 22 003
8 zz 66 003
1 小计 55 008
5 ff 55 008
例如:如果type=1没有记录
但也要有个汇总行