请问如何使用grouping实现小计、合计问题？

[echo_llee]

现有表table_name,字段有sail(记录销售情况，比如：打折、退货)、ISBN（记录商品编号，比如：00001 00002）、number记录数量、name记录商品名称。
现希望实现下述输出功能：
sail bh name number
打折 00001 铅笔 100
打折 00002 毛笔 200
打折 00003 油笔 300
小计 600
退货 00001 铅笔 -10
退货 00004 水彩笔 -50
小计 -60
合计 540
请问该如何写SQL语句?

[pabulum]

用datareporter去进行分组合计吧

[marvellin]

楼上的oracle写法正确

[echo_llee]

我是希望用一句话就能实现，可是，我调不通呢，怎么回事？

[xmao]

o

[smartdonkey]

select sail,name, sum(number )
from table_name
group by sail,name with rollup

[smartdonkey]

我的新的做法
create table m(sail varchar(20),bh char(10),name varchar(20),number int)

insert into m(sail,bh,name,number) values('打折','00001','铅笔',100)
insert into m(sail,bh,name,number) values('打折','00002','毛笔',300)
insert into m(sail,bh,name,number) values('打折','00003','油笔',400)
insert into m(sail,bh,name,number) values('退货','00001','铅笔',-10)
insert into m(sail,bh,name,number) values('退货','00004','水彩笔',-50)

--查询
select k.sail,k.bh,m.name,k.number from (select
case
when bh is null then
case
when sail is null then
'总计' else '小计'
end
else
sail
end sail
,bh, sum(number ) number
from m
group by sail,bh with rollup) k left join m on k.bh=m.bh

这样完全能得到您的结果

[mat]

如果是ORACLE中
select sail,bh,name,number from
((select sail,bh,name,number from table_sail
union all
select sail,'' bh,'小计' name,sum(number) number from table_sail group by sail)
union all
select '' sail,'' bh,'合计' name,sum(number) number from table_sail) a
order by sail,bh,name
一句就搞定了。
在SQL SERVER 中将union all 替换成union就可以了

[karma]

try this in SQL Server:

select sail, bh, name, number
from table_name
order by sail
compute sum(number) by sail