求一条SQL语句

[haozhangpk]

表A
number categoryid
20 1
30 1
50 2
60 1
80 2
我想得到下面的结果
number
20
30
60
90 //category = 1 的小计
50
80
130　//category = 2 的小计

[IDWB]

number
20
30
60
110 //category = 1 的小计 是吧？？？？？？？？？？？？？？？？
50
80
130　//category = 2 的小计

[bobohuang]

SELECT CASE WHEN (GROUPING(categoryid) = 1) THEN 'ALL'
ELSE ISNULL(categoryid, 'UNKNOWN')
END AS categoryid,
SUM(number) AS number
FROM 表A
GROUP BY categoryid WITH ROLLUP

[zjcxc]

--严格按楼主要求的结果:

select number from(
select * from 表A
union all
select number=sum(number),categoryid
from 表A
group by categoryid
) a order by categoryid,number

[zjcxc]

--下面是测试
declare @表A table(number int,categoryid int)
insert into @表A
select 20,1
union all select 30,1
union all select 50,2
union all select 60,1
union all select 80,2

--查询
select number from(
select * from @表A
union all
select number=sum(number),categoryid
from @表A
group by categoryid
) a order by categoryid,number

/*--测试结果
number
-----------
20
30
60
110
50
80
130

（所影响的行数为 7 行）
--*/

[haozhangpk]

还请zjcxc(邹建) 老大再次指点迷津，答完立刻给分

no name debit credit categoryid
1001 现金 12.00000000 212.00000000 1
1002 银行存款 200.00000000 NULL 1
1009 其它货币资金 NULL 100.00000000 1
1101 短期投资 500.00000000 NULL 1
1141 坏帐准备 NULL 500.00000000 1
2141 代销商品款 200.00000000 NULL 2
5601 营业外支出 NULL 100.00000000 4

我想得到的结果为
no name debit credit
1001 现金 12.00000000 212.00000000
1002 银行存款 200.00000000 NULL
1009 其它货币资金 NULL 100.00000000
1101 短期投资 500.00000000 NULL
1141 坏帐准备 NULL 500.00000000
712.00000000 812.00000000
2141 代销商品款 200.00000000 NULL
200.0000000
5601 营业外支出 NULL 100.00000000
100.0000000