create table t
(日期 datetime,净重 decimal(10,3))insert t
select '2005-3-3',323.000 union all
select '2005-3-4',233.000 union all
select '2005-3-5',477.000 union all
select '2005-3-6',585.000 union all
select '2005-3-7',744.000
go
create proc p_union
@s_time datetime,
@e_time datetime
as
begin
declare t_cursor cursor for select * from t where 日期 between @s_time and @e_time
declare @col1 datetime,@col2 decimal(10,3),@col3 decimal(10,3)
declare @t table (日期 varchar(30),净重 decimal(10,3))
set @col3=0
open t_cursor
fetch next from t_cursor into @col1,@col2
while @@fetch_status=0
begin
set @col3=@col3+@col2
if @col3>1000
begin
insert @t values (convert(varchar(10),@col1,120),@col2)
insert @t values (convert(varchar(10),@col1,120)+'合计',1000.000)
set @[email protected]
insert @t values (convert(varchar(10),@col1,120),@col3)
end
else
insert @t values (convert(varchar(10),@col1,120),@col2)
fetch next from t_cursor into @col1,@col2
end
CLOSE t_cursor
DEALLOCATE t_cursor
select * from @t
end
goexec p_union '2005-3-3','2005-3-7'drop proc p_union
drop table t
日期 净重
------------------------------ ------------
2005-03-03 323.000
2005-03-04 233.000
2005-03-05 477.000
2005-03-05合计 1000.000
2005-03-05 33.000
2005-03-06 585.000
2005-03-07 744.000
2005-03-07合计 1000.000
2005-03-07 362.000(所影响的行数为 9 行)
(日期 datetime,净重 decimal(10,3))insert t
select '2005-3-3',323.000 union all
select '2005-3-4',233.000 union all
select '2005-3-5',477.000 union all
select '2005-3-6',585.000 union all
select '2005-3-7',744.000
go
create proc p_union
@s_time datetime,
@e_time datetime
as
begin
declare t_cursor cursor for select * from t where 日期 between @s_time and @e_time
declare @col1 datetime,@col2 decimal(10,3),@col3 decimal(10,3)
declare @t table (日期 varchar(30),净重 decimal(10,3))
set @col3=0
open t_cursor
fetch next from t_cursor into @col1,@col2
while @@fetch_status=0
begin
set @col3=@col3+@col2
if @col3>1000
begin
insert @t values (convert(varchar(10),@col1,120),@col2)
insert @t values (convert(varchar(10),@col1,120)+'合计',1000.000)
set @[email protected]
insert @t values (convert(varchar(10),@col1,120),@col3)
end
else
insert @t values (convert(varchar(10),@col1,120),@col2)
fetch next from t_cursor into @col1,@col2
end
CLOSE t_cursor
DEALLOCATE t_cursor
select * from @t
end
goexec p_union '2005-3-3','2005-3-7'drop proc p_union
drop table t
日期 净重
------------------------------ ------------
2005-03-03 323.000
2005-03-04 233.000
2005-03-05 477.000
2005-03-05合计 1000.000
2005-03-05 33.000
2005-03-06 585.000
2005-03-07 744.000
2005-03-07合计 1000.000
2005-03-07 362.000(所影响的行数为 9 行)
create table t
(日期 datetime,净重 decimal(10,3))insert t
select '2005-3-3',323.000 union all
select '2005-3-4',233.000 union all
select '2005-3-5',477.000 union all
select '2005-3-6',585.000 union all
select '2005-3-7',744.000 union all
select '2005-3-8',344.000
go
create proc p_union
@s_time datetime,
@e_time datetime
as
begin
declare t_cursor cursor for select * from t where 日期 between @s_time and @e_time
declare @col1 datetime,@col2 decimal(10,3),@col3 decimal(10,3)
declare @t table (日期 varchar(30),净重 decimal(10,3))
set @col3=0
open t_cursor
fetch next from t_cursor into @col1,@col2
while @@fetch_status=0
begin
set @col3=@col3+@col2
if @col3>1000
begin
insert @t values (convert(varchar(10),@col1,120),@col2-(@col3-1000.000))
insert @t values ('合计',1000.000)
set @[email protected]
insert @t values (convert(varchar(10),@col1,120),@col3)
end
else
insert @t values (convert(varchar(10),@col1,120),@col2)
fetch next from t_cursor into @col1,@col2
end
CLOSE t_cursor
DEALLOCATE t_cursor
select * from @t
end
goexec p_union '2005-3-3','2005-3-8'drop proc p_union
drop table t
如果只用SQL本身来判断呢?
(日期 varchar(20),净重 decimal(10,3))insert t
select '11-3-3',1323.000 union all
select '11-3-4',233.000 union all
select '11-3-5',477.000 union all
select '11-3-6',585.000 union all
select '11-3-7',744.000
go
create proc p_union
@s_time varchar(20),
@e_time varchar(20)
as
begin
declare t_cursor cursor for select * from t where 日期 between @s_time and @e_time
declare @col1 varchar(20),@col2 decimal(10,3),@col3 decimal(10,3)
declare @t table (日期 varchar(30),净重 decimal(10,3))
set @col3=0
open t_cursor
fetch next from t_cursor into @col1,@col2
while @@fetch_status=0
begin
set @col3=@col3+@col2
if @col3>1000
begin
insert @t values (@col1,@col2-(@col3-1000.000))
insert @t values (@col1+'合计',1000.000)
set @[email protected]
insert @t values (@col1,@col3)
end
else
insert @t values (@col1,@col2)
fetch next from t_cursor into @col1,@col2
end
CLOSE t_cursor
DEALLOCATE t_cursor
select * from @t
end
goexec p_union '11-3-3','11-3-7'drop proc p_union
drop table t
/*-----------------为了测试特殊数据,我把第一个改成了1323.000----------*/日期 净重
------------------------------ ------------
11-3-3 1000.000
11-3-3合计 1000.000
11-3-3 323.000
11-3-4 233.000
11-3-5 444.000
11-3-5合计 1000.000
11-3-5 33.000
11-3-6 585.000
11-3-7 382.000
11-3-7合计 1000.000
11-3-7 362.000(所影响的行数为 11 行)
谢谢 samfeng_2003(凤翼天翔) ,