Question

用sql 如何实现 报表的分组的小计，合计！最后还有一个总计

Answer 1

如果要使用SQL来实现的话，可以采用临时表，但是这样必然会牺牲效率。

SELECT * INTO #TEMP FROM 表

INSERT INTO #TEMP VALUES(....) --这一句是你的小计、合计、总计

SELECT * FROM #TEMP

过程基本如此，需要注意的是临时表字段的类型，如果出现某字段不允许为空的话，可以采用如下语句来变更字段类型：
ALTER TABLE #TEMP
ALTER COLUMN 字段名 类型 NULL

最后要注意的是排序，要使得小计、合计、总计在结果集的最后。

Answer 2

写个例子你参考一下吧！！！

-----------------------------
if exists(select * from sysobjects where name='Test' and type='u')
drop table Test
go
create table Test
(
Item nvarchar(50),
Color nvarchar(50),
Quantity int
)

--------------------------------

insert into Test
select 'Table','Blue',124
union
select 'Table','Red',223
union
select 'Chair','Blue',101
union
select 'Chair','Red',210
union
select 'Window','Blue',222
union
select 'Window','Blue',333

-------------------------
create table #tmp1
(
Item nvarchar(50),
Color nvarchar(50),
Quantity int,
FID int identity
)

insert into #tmp1
select Item,Color,Quantity from test order by Item,Color

create table #tmp2
(
Item nvarchar(50),
Color nvarchar(50),
Quantity int,
FID int identity

)

declare @Item nvarchar(50)
declare @Color nvarchar(50)
declare @Quantity int

declare @CountQuan int
declare @PItem nvarchar(50)
declare @PColor nvarchar(50)

declare @i int
declare @count int

set @count=(select count(*) from #tmp1)
set @i=1

set @Item=(select Item from #tmp1 where fid=@i)
set @Color=(select Color from #tmp1 where fid=@i)
set @Quantity=(select Quantity from #tmp1 where fid=@i)

set @PItem=@Item
set @PColor=@Color
set @CountQuan=@Quantity

while @i<=@count
begin

insert into #tmp2 values(@Item,@Color,@Quantity)
if @i=@count
begin
break
end
set @i=@i+1

set @Item=(select Item from #tmp1 where fid=@i)
set @Color=(select Color from #tmp1 where fid=@i)
set @Quantity=(select Quantity from #tmp1 where fid=@i)

if @PItem<>@Item or @PColor<>@Color
begin
insert into #tmp2 values('小计',@PColor,@CountQuan)
set @CountQuan=0
set @PColor=@Color
set @PItem=@Item
end
set @CountQuan=@CountQuan+@Quantity
end
insert into #tmp2 values('小计',@PColor,@CountQuan)

declare @Sum as int
set @Sum=(select sum(Quantity) from #tmp2
where Item='小计' and Quantity is not null
)

if (SELECT count(*) FROM #tmp2)=1
DELETE FROM #tmp2

if (SELECT count(*) FROM #tmp2)<>0
insert into #tmp2 values('总计','总计全部',@Sum)

insert into #tmp2
select '合计',Color,sum(Quantity)
from #tmp2 where Item not like '%小计%' and Item not like '%总计%' group by Color

-----------------------------------------------------------------------------------------------------------------------------------------------------------
select Item,Color,Quantity
from #tmp2
order by Color,FID

drop table #tmp1
drop table #tmp2

把以上代码黏贴到sql 查询分析器里执行一下，看看是不是你想要的结果