用sql 如何实现 报表的分组的小计,合计!最后还有一个总计
发布网友
发布时间：2022-04-24 02:12
共2个回答
热心网友
时间：2022-04-08 10:55
如果要使用SQL来实现的话，可以采用临时表，但是这样必然会牺牲效率。
SELECT * INTO #TEMP FROM 表
INSERT INTO #TEMP VALUES(....) --这一句是你的小计、合计、总计
SELECT * FROM #TEMP
过程基本如此，需要注意的是临时表字段的类型，如果出现某字段不允许为空的话，可以采用如下语句来变更字段类型：
ALTER TABLE #TEMP
ALTER COLUMN 字段名 类型 NULL
最后要注意的是排序，要使得小计、合计、总计在结果集的最后。
热心网友
时间：2022-04-08 12:13
写个例子你参考一下吧！！！
-----------------------------
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 查询分析器里执行一下，看看是不是你想要的结果
