请教高手数据库sql server2000

[昆曲之源_娄江河畔]

问题: 有一表temp
字段 编号 名称 数量
0001-1001 书1 1
0001-1002 书2 2
0002-1001 书3 1
要求:汇总排序形成:
0001-1001 书1 1
0001-1002 书2 2
0001 小计 3
0002-1001 书3 1
0002 小计 1
合计 4

[昆曲之源_娄江河畔]

谢谢 Chiff OK
该结了

[日总是我哥]

Select * From (
Select * From Table1
union all
Select SubString(ID, 1, 4) as ID, '小计' as Name, sum(qty) as qty
from table1
Group By SubString(ID, 1, 4)
union all
Select '9999' as ID, '总计' as Name, sum(qty) as qty
from table1
) as F
Order By SubString(ID, 1, 4)

///////那个总计不知道怎么排:(

[愉快的登山者]

create table #temp (
id int identity (1,1),
bh char (10),
name char (10),
books int
)
go
insert #temp valuse (bh,name,books) select 字段+编号,名称,数量 from temp where 字段='0001' order by 编号
go
insert #temp valuse (bh,name,books) select 字段,‘小计’,sum(数量) from temp group by 字段 where 字段='0001'
go
insert #temp valuse (bh,name,books) select 字段+编号,名称,数量 from temp where 字段='0002' order by 编号
go
insert #temp valuse (bh,name,books) select 字段,‘小计’,sum(数量) from temp group by 字段 where 字段='0002'
go
insert #temp valuse (bh,name,books) select ‘’,‘合计’,sum(数量) from temp
go
select bh,name,books from #temp
go
若记录比较多，可以采用循环处理。

[Chiff]

呵呵...
Select case id when 9999 then '' else id end
From (
Select * From Table1
union all
Select SubString(ID, 1, 4) as ID,
'小计' as Name, sum(qty) as qty
from table1
Group By SubString(ID, 1, 4)
union all
Select '9999' as ID, '总计' as Name, sum(qty) as qty
from table1
) as F
Order By SubString(ID, 1, 4),
case substring(id,5,2) when ' ' then '99' else id)

[愉快的登山者]

修改：
select bh,name,books from #temp order by id
go

[Chiff]

Select * From (
Select * From Temp
union all
Select left(编号,4),'小计',sum(数量)
from temp group by left(编号,4)
union all
Select 'zzzz', '总计' ,sum(数量)
from temp
) as F
Order By left(编号,4),len(rtrim(编号)) desc,编号
case substring(id,5,2) when ' ' then '99' else id)

[Chiff]

--不好意思，把不要贴的贴出来了。
Select case 编号 when 'zzzz' then '' else 编号 end,名称,数量
From (
Select * From Temp
union all
Select left(编号,4),'小计',sum(数量)
from temp group by left(编号,4)
union all
Select 'zzzz', '总计' ,sum(数量)
from temp
) as F
Order By left(编号,4),len(rtrim(编号)) desc,编号