求SQL算法

Question

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       51802 1030141       0
2007-12-06 11:51:42.610       51802 1030141       1
2007-12-05 11:45:30.420       51802 1030468       0
2007-12-05 11:46:36.560       51802 1030468       1
2007-12-06 12:01:18.530       51802 1030468       0
2007-12-09 14:09:30.840       51802 1030468       1
2007-12-12 14:12:59.967       51802 1030468       0
2007-12-13 14:25:35.577       51802 1030468       1
2007-12-13 14:26:21.480       51802 1030468       0
2007-12-14 14:26:47.840       51802 1030468       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')提示：
如果能得到下面结果集，就很容易计算出来了。
51802 1030468 '2007-12-05'  '2007-12-05'
51802 1030468 '2007-12-06'  '2007-12-09'
51802 1030468 '2007-12-12'  '2007-12-13'
51802 1030468 '2007-12-13'  '2007-12-14'要求：用一条语句得到，不管嵌套SQL多深，我不想用存储过程实现。

Answer 1

找了半天也没有找到inoutqty这一列在哪楼主，你的表结构跟插入语句好像有点对不上。

Answer 2

不好意思，发错了，改过。CREATE TABLE [u_inout_break] (
[execdate] [datetime] NOT NULL ,
[billcode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[busno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[wareid] [varchar] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[billno] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[flag] [tinyint] NOT NULL ,
[inoutqty] [numeric](16, 6) NOT NULL ,
[endqty] [numeric](16, 6) NOT NULL ,
[stamp] [timestamp] NOT NULL ,
 PRIMARY KEY  CLUSTERED 
(
[execdate],
[billcode],
[busno],
[wareid],
[billno],
[flag]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO

Answer 3

gthlu 
风子 
等 级：
 发表于：2007-12-05 15:52:443楼 得分:0 
前面两段是可以直接运行的，方便调试，大家可以运行后查询看看结果   。 
 
============================================倒，你测试过了吗？为啥米俺的有错误提示尼？Msg 207, Level 16, State 1, Line 1
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'endqty'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'inoutqty'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'endqty'.

Answer 4

不好意思，发错了，改过。 CREATE   TABLE   [u_inout_break]   ( 
[execdate]   [datetime]   NOT   NULL   , 
[billcode]   [varchar]   (10)   COLLATE   Chinese_PRC_CI_AS   NOT   NULL   , 
[busno]   [varchar]   (10)   COLLATE   Chinese_PRC_CI_AS   NOT   NULL   , 
[wareid]   [varchar]   (13)   COLLATE   Chinese_PRC_CI_AS   NOT   NULL   , 
[billno]   [varchar]   (30)   COLLATE   Chinese_PRC_CI_AS   NOT   NULL   , 
[flag]   [tinyint]   NOT   NULL   , 
[inoutqty]   [numeric](16,   6)   NOT   NULL   , 
[endqty]   [numeric](16,   6)   NOT   NULL   , 
[stamp]   [timestamp]   NOT   NULL   , 
  PRIMARY   KEY     CLUSTERED   
( 
[execdate], 
[billcode], 
[busno], 
[wareid], 
[billno], 
[flag] 
)     ON   [PRIMARY]   
)   ON   [PRIMARY] 
GO 
:) :)  :)  :)

Answer 5

修改了一下楼主的插入语句--插入数据
  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 a
where flag = 0--结果
busno      wareid        execdate                execdate_end
---------- ------------- ----------------------- -----------------------
51802      1030141       2007-12-03 11:49:00.000 2007-12-05 11:46: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 row(s) affected)

Answer 6

--我截去了你多余的字段和数据。
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 - 1drop 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 行）
*/

Answer 7

set nocount on
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 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 days
from (
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 d
from u_inout_break a
where a.flag = 0
) aa
group by wareid
--group by wareid,drop table u_inout_break/*wareid        days        
------------- ----------- 
1030141       3
1030468       5*/

Answer 8

潇洒老乌龟：不好意思，我的要求是用一条查询语句，可你用到了identity，这样就肯定实现不了。还是“冷箫轻笛”的算法好，不过咱以前也这样用过，但这回怎没想到呢，呵呵，多话了。给分。

Answer 9

select busno,wareid,s=(select max(execdate) from u_inout_break where busno=a.busno and wareid=a.wareid and execdate<a.execdate),execdate 
from  u_inout_break a where flag=1 order by busno , wareid ,s
busno      wareid        s                       execdate
---------- ------------- ----------------------- -----------------------
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