一个面试题
需求是这样的
id 日期 货物数量
1 2011/1/2 10
2 2012/12/12 10
3 。。。。。。 。
输入你要提取的货物数量 先从日期最早的开始取
假如要取 15个 就先从 日期为 2011/1/2 中取 10
然后再从 日期为2012/12/12 取 5个 这个sql语句应该怎么写啊 那要是去25个呢?
[解决办法]
create table tb(id int,dt datetime,num int)
insert into tb select 1,'2011/1/2',10
insert into tb select 2,'2012/12/12',10
insert into tb select 3,'2012/12/15',10
insert into tb select 4,'2012/12/25',10
go
declare @num int
set @num=25
--如果id不是连续的,则用row_number建一个
;with cte as(
select id,dt,(case when num<@num then num else @num end)as n,num-@num as f from tb where id=1
union all
select a.id,a.dt,(case when a.num+b.f<0 then a.num else -b.f end),a.num+b.f
from tb a inner join cte b on a.id=b.id+1 where b.f<0
)select id,dt,n from cte
/*
id dt n
----------- ----------------------- -----------
1 2011-01-02 00:00:00.000 10
2 2012-12-12 00:00:00.000 10
3 2012-12-15 00:00:00.000 5
(3 行受影响)
*/
go
drop table tb
[解决办法]
create table tb_test
(
f_id varchar(10),
f_rq datetime,
f_sl float
)
insert into tb_test
select 1,convert(datetime,'2012-11-01'),5
union all
select 2,convert(datetime,'2012-11-02'),10
union all
select 3,convert(datetime,'2012-11-03'),15
select * from tb_test
declare @sl int
set @sl=10
while @sl>0
begin
declare @bl int
set @bl=(select f_sl from tb_test where f_rq=(select min(f_rq) from tb_test where f_sl<>0))
if
@sl<@bl
break
else
set @sl=(select @sl-f_sl from tb_test where f_rq=(select min(f_rq) from tb_test where f_sl<>0))
update tb_test set f_sl=0 where f_rq=(select min(f_rq) from tb_test where f_sl<>0)
end
update tb_test set f_sl=f_sl-@sl where f_rq=(select min(f_rq) from tb_test where f_sl<>0)
set @sl=0
drop table tb_test