求一条单表存储过程，绝对够难度

数据：

id   起始时间(datetime)        结束时间(datetime)        共计时间(float)   用掉(float)
1    2008-08-23 18:30:00.000   2008-08-23 20:40:00.000   2                 2
2    2008-11-03 19:00:00.000   2008-11-03 22:00:00.000   3                 3
3    2008-12-04 21:00:00.000   2008-12-04 23:00:00.000   2                 1
4    2008-12-22 10:00:00.000   2008-12-22 13:00:00.000   3                 null
5    2009-03-02 10:00:00.000   2009-03-02 13:45:00.000   3.5               null
6    2009-07-03 10:00:00.000   2009-07-03 11:45:00.000   1.5               null

存储过程接收两个参数：@d和@t

例如，接收参数为：2008-10-05和5两个，那么先查询（排序按[起始时间]从早到晚）出比@d晚(只精确到天)的所有记录（也就是2-6），然后再判断，如果这个记录集中存在[用掉]字段不为null且不等于[共计时间]字段的记录，那么只保留这条记录之后的记录（包括这条记录本身），如果所有[用掉]字段均为null，则保持原状。所以这时记录集为3-6

然后遍历该记录集

如果第1条记录的[共计时间]-[用掉]<@t，那么@t-([共计时间]-[用掉])，并将第1条记录的[用掉]补全（就是更新这条记录的[用掉]使之等于[起始时间]），结果为：
3    2008-12-04 21:00:00.000   2008-12-04 23:00:00.000   2                 2

然后往下（现在的@t=4），第2条记录的[共计时间]为3，那么@t-3，并补全这条记录
4    2008-12-22 10:00:00.000   2008-12-22 13:00:00.000   3                 3

现在的@t为1，第3条记录的[共计时间]为3.5，那么@t-3.5，并补全记录：
5    2009-03-02 10:00:00.000   2009-03-02 13:45:00.000   3.5               1

直到@t为0。

说了这么多，其实就是把得到的@t补全到记录中，按时间从早到晚，[用掉]不够的加满

请教这条存储过程该怎么写，越简单越好，谢谢

22 个解决方案

#1

这个难 有请P梁

#2

#3

#4

怎么看不懂

#5

感觉大家没看懂啊，简单点说就是原记录为：
id  起始时间(datetime)        结束时间(datetime)        共计时间(float)  用掉(float)
1    2008-08-23 18:30:00.000  2008-08-23 20:40:00.000  2                2
2    2008-11-03 19:00:00.000  2008-11-03 22:00:00.000  3                3
3    2008-12-04 21:00:00.000  2008-12-04 23:00:00.000  2                1
4    2008-12-22 10:00:00.000  2008-12-22 13:00:00.000  3                null
5    2009-03-02 10:00:00.000  2009-03-02 13:45:00.000  3.5              null
6    2009-07-03 10:00:00.000  2009-07-03 11:45:00.000  1.5              null 

得到一个参数@t，假设为5，那么我把这个5分配到记录中的[用掉]字段中，变成：

id  起始时间(datetime)        结束时间(datetime)        共计时间(float)  用掉(float)
1    2008-08-23 18:30:00.000  2008-08-23 20:40:00.000  2                2
2    2008-11-03 19:00:00.000  2008-11-03 22:00:00.000  3                3
3    2008-12-04 21:00:00.000  2008-12-04 23:00:00.000  2                2
4    2008-12-22 10:00:00.000  2008-12-22 13:00:00.000  3                3
5    2009-03-02 10:00:00.000  2009-03-02 13:45:00.000  3.5              1
6    2009-07-03 10:00:00.000  2009-07-03 11:45:00.000  1.5              null 

#6

有请小梁。

#7

呵呵，感觉就是一个网吧的交钱买时间>上机用时间

之类的东西

后面的逻辑有点问题，如果@t减不到0怎么办？比如这个例子里@t等于10

选到3-6结果集初级SQL选手应该都没问题

接下来的东西不想个等效算法的话只能用CTE递归了

#8

--测试数据
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[起始时间] datetime,[结束时间] datetime,[共计时间] float,[用掉] float)
insert into [tb]
select 1,'2008-08-23 18:30:00.000','2008-08-23 20:40:00.000',2,2 union all
select 2,'2008-11-03 19:00:00.000','2008-11-03 22:00:00.000',3,3 union all
select 3,'2008-12-04 21:00:00.000','2008-12-04 23:00:00.000',2,1 union all
select 4,'2008-12-22 10:00:00.000','2008-12-22 13:00:00.000',3,null union all
select 5,'2009-03-02 10:00:00.000','2009-03-02 13:45:00.000',3.5,null union all
select 6,'2009-07-03 10:00:00.000','2009-07-03 11:45:00.000',1.5,null

--创建存储过程
create proc proc_trans_make @d varchar(10),@t int
as
declare @all float,@use float,@i int
select @i = 1
select identity(int,1,1) as 序号,* into #temp
from [tb]
where convert(varchar(10),[起始时间],120) >= @d
and (共计时间 <> 用掉 or 用掉 is null)
order by 起始时间
while @t >= 1
begin
	select @all = [共计时间],@use = isnull([用掉],0) from #temp
	where [序号] = @i
	if @all - @use < @t
	begin
		update #temp set [用掉] = [共计时间] where [序号] = @i
	end
	else
	begin
		update #temp set [用掉] = @t where [序号] = @i
	end
	select @t = @t - (@all - @use),
	       @i = @i + 1	
end

select * from #temp

--调用
proc_trans_make '2008-10-05',5
--结果
-------------------------------------------
1	3	2008-12-04 21:00:00.000	2008-12-04 23:00:00.000	2.0	2.0
2	4	2008-12-22 10:00:00.000	2008-12-22 13:00:00.000	3.0	3.0
3	5	2009-03-02 10:00:00.000	2009-03-02 13:45:00.000	3.5	1.0
4	6	2009-07-03 10:00:00.000	2009-07-03 11:45:00.000	1.5	NULL

#9

引用 8 楼 nianran520 的回复:

SQL code--测试数据ifobject_id('[tb]')isnotnulldroptable[tb]createtable[tb]([id]int,[起始时间]datetime,[结束时间]datetime,[共计时间]float,[用掉]float)insertinto[tb]select1,'2008-08-23 18:30:00.000','2008-08-23 20:40:00.?-

膜拜

#10

--入参@t数据类型应改为float，自己改改

#11

……大家快出来看上帝

#12

还差点，楼主要更新原纪录，不是返回临时表

#13

--还有点问题
while @t >= 1
改为
while @t >= 0

#14

-更新原表就改为这个
alter proc proc_trans_make @d varchar(10),@t float
as
declare @all float,@use float,@i int
select @i = 1
select identity(int,1,1) as 序号,* into #temp
from [tb]
where convert(varchar(10),[起始时间],120) >= @d
and (共计时间 <> 用掉 or 用掉 is null)
order by 起始时间
while @t >= 0
begin
	select @all = [共计时间],@use = isnull([用掉],0) from #temp
	where [序号] = @i
	if @all - @use < @t
	begin
		update #temp set [用掉] = [共计时间] where [序号] = @i
	end
	else
	begin
		update #temp set [用掉] = @t where [序号] = @i
	end
	select @t = @t - (@all - @use),
	       @i = @i + 1	
end

update tb set [用掉] = t.[用掉]
from tb r join #temp t
on r.id = t.id

#15

差不多了，楼主结贴吧~

#16

看看我的结贴率，绝对对得起观众

我试试先

#17

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-04 14:54:54
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @tb
declare @tb table (id int,起始时间 datetime,结束时间 datetime,共计时间 numeric(2,1),用掉 numeric(2,1))
insert into @tb
select 1,'2008-08-23 18:30:00.000','2008-08-23 20:40:00.000',2,2 union all
select 2,'2008-11-03 19:00:00.000','2008-11-03 22:00:00.000',3,3 union all
select 3,'2008-12-04 21:00:00.000','2008-12-04 23:00:00.000',2,1 union all
select 4,'2008-12-22 10:00:00.000','2008-12-22 13:00:00.000',3,null union all
select 5,'2009-03-02 10:00:00.000','2009-03-02 13:45:00.000',3.5,null union all
select 6,'2009-07-03 10:00:00.000','2009-07-03 11:45:00.000',1.5,null


declare @s decimal(10,1),@s1 decimal(10,1)
set @s=5


update @tb 
set @s1=case when 共计时间-isnull(用掉,0)>@s and @s>0 then  @s
			 when 共计时间-isnull(用掉,0)<=@s and @s>0 then   共计时间-isnull(用掉,0) 
		end,用掉=@s1,@s=@s-@s1
where 共计时间-isnull(用掉,0)>0


select * from @tb

id          起始时间                    结束时间                    共计时间                                    用掉
----------- ----------------------- ----------------------- --------------------------------------- ---------------------------------------
1           2008-08-23 18:30:00.000 2008-08-23 20:40:00.000 2.0                                     2.0
2           2008-11-03 19:00:00.000 2008-11-03 22:00:00.000 3.0                                     3.0
3           2008-12-04 21:00:00.000 2008-12-04 23:00:00.000 2.0                                     1.0
4           2008-12-22 10:00:00.000 2008-12-22 13:00:00.000 3.0                                     3.0
5           2009-03-02 10:00:00.000 2009-03-02 13:45:00.000 3.5                                     1.0
6           2009-07-03 10:00:00.000 2009-07-03 11:45:00.000 1.5                                     NULL

(6 行受影响)

#18

jf

#19

declare @s decimal(10,1),@s1 decimal(10,1)
set @s=5


update @tb 
set @s1=case when 共计时间-isnull(用掉,0)>@s and @s>0 then  @s
			 when 共计时间-isnull(用掉,0)<=@s and @s>0 then   共计时间-isnull(用掉,0) 
		end,
		用掉=isnull(用掉,0)+@s1,@s=@s-@s1         ---这句修改一下
where 共计时间-isnull(用掉,0)>0


select * from @tb

id          起始时间                    结束时间                    共计时间                                    用掉
----------- ----------------------- ----------------------- --------------------------------------- ---------------------------------------
1           2008-08-23 18:30:00.000 2008-08-23 20:40:00.000 2.0                                     2.0
2           2008-11-03 19:00:00.000 2008-11-03 22:00:00.000 3.0                                     3.0
3           2008-12-04 21:00:00.000 2008-12-04 23:00:00.000 2.0                                     2.0
4           2008-12-22 10:00:00.000 2008-12-22 13:00:00.000 3.0                                     3.0
5           2009-03-02 10:00:00.000 2009-03-02 13:45:00.000 3.5                                     1.0
6           2009-07-03 10:00:00.000 2009-07-03 11:45:00.000 1.5                                     NULL

(6 行受影响)

#20

面对那么多的高人，我紧张了，。。。不懂怎么办？

#21

declare @tb table (id int,起始时间 datetime,结束时间 datetime,共计时间 numeric(2,1),用掉 numeric(2,1))
insert into @tb
select 1,'2008-08-23 18:30:00.000','2008-08-23 20:40:00.000',2,2 union all
select 2,'2008-11-03 19:00:00.000','2008-11-03 22:00:00.000',3,3 union all
select 3,'2008-12-04 21:00:00.000','2008-12-04 23:00:00.000',2,1 union all
select 4,'2008-12-22 10:00:00.000','2008-12-22 13:00:00.000',3,null union all
select 5,'2009-03-02 10:00:00.000','2009-03-02 13:45:00.000',3.5,null union all
select 6,'2009-07-03 10:00:00.000','2009-07-03 11:45:00.000',1.5,null

DECLARE @N INT
SET @N=5
declare buchong cursor for
select id,共计时间-isnull(用掉,0) as 剩下 from @tB where  共计时间-isnull(用掉,0)<>0
OPEN BUCHONG 
declare @sy int,@k int ,@id int
set @sy=@N
fetch next from buchong into @id,@k
while @@FETCH_STATUS=0 and @sy>=0
begin 
	update @tb 
	set 用掉=isnull(用掉,0)+case when @sy-@k>=0 then @k else @sy end
    where id=@id
    set @sy=@sy-@k
   fetch next from buchong into @id,@k
end
close buchong
deallocate buchong
/*
id          起始时间                    结束时间                    共计时间                                    用掉
----------- ----------------------- ----------------------- --------------------------------------- ---------------------------------------
1           2008-08-23 18:30:00.000 2008-08-23 20:40:00.000 2.0                                     2.0
2           2008-11-03 19:00:00.000 2008-11-03 22:00:00.000 3.0                                     3.0
3           2008-12-04 21:00:00.000 2008-12-04 23:00:00.000 2.0                                     2.0
4           2008-12-22 10:00:00.000 2008-12-22 13:00:00.000 3.0                                     3.0
5           2009-03-02 10:00:00.000 2009-03-02 13:45:00.000 3.5                                     1.0
6           2009-07-03 10:00:00.000 2009-07-03 11:45:00.000 1.5                                     NULL*/

select * from @tb

游标写个 呵呵