求SQL算法
TABLE:
create table u_inout_break(
execdate datetime not null,
billcode varchar(10) not null,
busno varchar(10) not null,
wareid varchar(13) not null,
billno varchar(30) not null,
flag tinyint not null, --0：缺货；1：到货
storeqty numeric(16,6) not null, --业务发生后的库存余额
stamp timestamp not null
primary key(execdate,billcode,busno,wareid,billno,flag)
)
表内容：（可直接运行以便调试）
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 3 2007 11:49AM","WHL","51802","1030141","",0,-100.000000,0.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 5 2007 11:45AM","WHL","51802","1030468","",0,-450.000000,0.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 5 2007 11:46AM","acc","51802","1030468","",1,100.000000,100.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 6 2007 11:51AM","acc","51802","1030141","",1,230.000000,230.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 6 2007 12:01PM","WHL","51802","1030468","",0,-200.000000,0.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 9 2007 2:09PM","acc","51802","1030468","",1,12.000000,12.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 12 2007 2:12PM","WHL","51802","1030468","",0,-12.000000,0.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 13 2007 2:25PM","acc","51802","1030468","",1,23.000000,23.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 13 2007 2:26PM","WHL","51802","1030468","",0,-23.000000,0.000000)
INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values("12 14 2007 2:26PM","acc","51802","1030468","",1,34.000000,34.000000)
表内容如下：
时间 业务单位 商品编码 标志
2007-12-03 11:49:45.873 518021030141 0
2007-12-06 11:51:42.610 518021030141 1
2007-12-05 11:45:30.420 518021030468 0
2007-12-05 11:46:36.560 518021030468 1
2007-12-06 12:01:18.530 518021030468 0
2007-12-09 14:09:30.840 518021030468 1
2007-12-12 14:12:59.967 518021030468 0
2007-12-13 14:25:35.577 518021030468 1
2007-12-13 14:26:21.480 518021030468 0
2007-12-14 14:26:47.840 518021030468 1
问题：
“标志”字段为0的说明是某商品断货时间，“标志”为1的说明是某商品的到货时间，某商品在一段时间内会有多次的断货，我想计算某业务单位，某商品的断货天数。
如：商品1030468的总计断货天数为：
select datediff(dd,'2007-12-05','2007-12-05') +
datediff(dd,'2007-12-06','2007-12-09') +
datediff(dd,'2007-12-12','2007-12-13') +
datediff(dd,'2007-12-13','2007-12-14')
提示：
如果能得到下面结果集，就很容易计算出来了。
518021030468 '2007-12-05' '2007-12-05'
518021030468 '2007-12-06' '2007-12-09'
518021030468 '2007-12-12' '2007-12-13'
518021030468 '2007-12-13' '2007-12-14'
要求：用一条语句得到，不管嵌套SQL多深，我不想用存储过程实现。
[解决办法]
修改了一下楼主的插入语句
--插入数据 INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-03 11:49','WHL','51802','1030141','',0,-100.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-05 11:45','WHL','51802','1030468','',0,-450.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-05 11:46','acc','51802','1030468','',1,100.000000,100.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-06 11:51','acc','51802','1030141','',1,230.000000,230.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-06 12:01','WHL','51802','1030468','',0,-200.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-09 14:09','acc','51802','1030468','',1,12.000000,12.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-12 14:12','WHL','51802','1030468','',0,-12.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-13 14:25','acc','51802','1030468','',1,23.000000,23.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-13 14:26','WHL','51802','1030468','',0,-23.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,inoutqty,endqty) values('2007-12-14 14:26','acc','51802','1030468','',1,34.000000,34.000000) --查询select busno,wareid,execdate, execdate_end = (select min(execdate) from u_inout_break where flag = 1 and execdate > a.execdate)from u_inout_break awhere flag = 0
[解决办法]
--我截去了你多余的字段和数据。
create table tb(
execdate datetime,
billcode varchar(10),
busno varchar(10),
flag tinyint, --0：缺货；1：到货
)
INSERT into tb(execdate,billcode,busno,flag) values('12 3 2007 11:49AM','51802','1030141',0)
INSERT into tb(execdate,billcode,busno,flag) values('12 5 2007 11:45AM','51802','1030468',0)
INSERT into tb(execdate,billcode,busno,flag) values('12 5 2007 11:46AM','51802','1030468',1)
INSERT into tb(execdate,billcode,busno,flag) values('12 6 2007 11:51AM','51802','1030141',1)
INSERT into tb(execdate,billcode,busno,flag) values('12 6 2007 12:01PM','51802','1030468',0)
INSERT into tb(execdate,billcode,busno,flag) values('12 9 2007 2:09PM' ,'51802','1030468',1)
INSERT into tb(execdate,billcode,busno,flag) values('12 12 2007 2:12PM','51802','1030468',0)
INSERT into tb(execdate,billcode,busno,flag) values('12 13 2007 2:25PM','51802','1030468',1)
INSERT into tb(execdate,billcode,busno,flag) values('12 13 2007 2:26PM','51802','1030468',0)
INSERT into tb(execdate,billcode,busno,flag) values('12 14 2007 2:26PM','51802','1030468',1)
go
--生成一临时表，不用临时表的随后到。
select id = identity(int,1,1) ,* into tmp from tb order by billcode , busno , execdate , flag
select a.billcode , a.busno , a.execdate date1 , b.execdate date2 from tmp a , tmp b where a.billcode = b.billcode and a.busno = b.busno and a.flag = 0 and a.id = b.id - 1
drop table tb,tmp
/*
billcode busno date1 date2
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
51802 1030141 2007-12-03 11:49:00.000 2007-12-06 11:51:00.000
51802 1030468 2007-12-05 11:45:00.000 2007-12-05 11:46:00.000
51802 1030468 2007-12-06 12:01:00.000 2007-12-09 14:09:00.000
51802 1030468 2007-12-12 14:12:00.000 2007-12-13 14:25:00.000
51802 1030468 2007-12-13 14:26:00.000 2007-12-14 14:26:00.000
（所影响的行数为 5 行）
*/
set nocount oncreate table u_inout_break( execdate datetime not null, billcode varchar(10) not null, busno varchar(10) not null, wareid varchar(13) not null, billno varchar(30) not null, flag tinyint not null, --0：缺货；1：到货 storeqty numeric(16,6) not null, --业务发生后的库存余额 stamp int not null primary key(execdate,billcode,busno,wareid,billno,flag) ) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 3 2007 11:49AM','WHL','51802','1030141','',0,-100.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 5 2007 11:45AM','WHL','51802','1030468','',0,-450.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 5 2007 11:46AM','acc','51802','1030468','',1,100.000000,100.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 6 2007 11:51AM','acc','51802','1030141','',1,230.000000,230.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 6 2007 12:01PM','WHL','51802','1030468','',0,-200.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 9 2007 2:09PM','acc','51802','1030468','',1,12.000000,12.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 12 2007 2:12PM','WHL','51802','1030468','',0,-12.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 13 2007 2:25PM','acc','51802','1030468','',1,23.000000,23.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 13 2007 2:26PM','WHL','51802','1030468','',0,-23.000000,0.000000) INSERT u_inout_break(execdate,billcode,busno,wareid,billno,flag,storeqty,stamp) values('12 14 2007 2:26PM','acc','51802','1030468','',1,34.000000,34.000000) goselect wareid,sum(d) as daysfrom (select wareid,datediff(d,execdate,(select top 1 execdate from u_inout_break where busno=a.busno and wareid= a.wareid and billno= a.billno and execdate >a.execdate and flag = 1 order by execdate) ) as dfrom u_inout_break awhere a.flag = 0) aagroup by wareid--group by wareid,drop table u_inout_break/*wareid days ------------- ----------- 1030141 31030468 5*/