请教一个查询,查询货品已连续多少天有出货记录(要有效率)

Question

--如何得到这样一个顺序号?/*
如何根据销货事务明细表,查询到货品编码已连续多少天有出货记录,
当有日期中断时,从1开始计数
主要条件:1:任一货品一天可以有多条出货记录
2:任一货品也可多天没有一条出货记录
销货事务明细表(主要字段 货品编码 + 出货数量 + 出货日期 + 出货单价 + 出货单号)
*/create table t_tran(id int identity(1,1), --唯一行号
item_no varchar(60) null,--货品编码
sl numeric(20,4) null,--出货数量
dt datetime null,--出货日期
price numeric(20,4) null,--出货单价
invoice varchar(50) null--出货单号
)
goinsert into t_tran(item_no,sl,dt,price,invoice)
select '0001',10,'2006-08-01',50,'io0001'
union
select '0001',20,'2006-08-02',50,'io0003'
union
select '0002',5,'2006-08-01',100,'io0005'
union
select '0002',8,'2006-08-03',100,'io0008'
union
select '0002',6,'2006-08-04',100,'io0015'
union
select '0001',3,'2006-08-06',50,'io0055'
union
select '0001',16,'2006-08-07',50,'io0045'
union
select '0001',17,'2006-08-08',50,'io0019'
union
select '0001',15,'2006-08-08',50,'io0029'
union
select '0001',18,'2006-08-09',50,'io0089'
union
select '0001',10,'2006-08-10',50,'io0099'
go--根据销货事务明细表,查询到货品编码已连续多少天有出货记录,结果大致如下:(货品编码+日期+连续多少天)
--输入参数:货品编码(多个) + 日期区间(开始日期及结束日期)
--输出结果:
/*
item_no    dt            days
'0001'   '2006-08-01'    1
'0001'   '2006-08-02'    2--注:1号有出货记录,2号也有,因此连续天数为2
'0001'   '2006-08-06'    1--注:3-5号0001货品没有出货,此处从1开始计数
'0001'   '2006-08-07'    2
'0001'   '2006-08-08'    3
'0001'   '2006-08-09'    4
'0001'   '2006-08-10'    5
'0002'   '2006-08-01'    1
'0002'   '2006-08-03'    1--注:2号0002货品没有出货,此处从1开始计数
'0002'   '2006-08-04'    2
*/

Answer 1

try：select
    a.item_no,a.dt,datediff(dd,max(b.dt),a.dt)+1
from
    (select 
         distinct b.item_no,b.dt 
     from 
         t_tran b) a,
    (select 
         distinct c.item_no,c.dt 
     from 
         t_tran c 
     where 
         not exists(select 1 from t_tran where item_no=c.item_no and datediff(dd,c.dt,dt)=1)) d
where
    a.item_no=d.item_no and a.dt>=d.dt
group by
    a.item_no,a.dt

Answer 2

select item_no,dt,cast(0 as int) as days
into #t
from t_tran a
group by item_no,dt
order by item_no,dt
declare @dt datetime
declare @item_no varchar(10)
declare @days int
set @days=0update #t
   set @days=case when @item_no=item_no and dt=dateadd(day,1,@dt) then @days+1 else 1 end,
       @item_no=item_no,@dt=dt,days=@daysselect * from #t
         
drop table #t
--结果
item_no                                                      dt                                                     days        
------------------------------------------------------------ ------------------------------------------------------ ----------- 
0001                                                         2006-08-01 00:00:00.000                                1
0001                                                         2006-08-02 00:00:00.000                                2
0001                                                         2006-08-06 00:00:00.000                                1
0001                                                         2006-08-07 00:00:00.000                                2
0001                                                         2006-08-08 00:00:00.000                                3
0001                                                         2006-08-09 00:00:00.000                                4
0001                                                         2006-08-10 00:00:00.000                                5
0002                                                         2006-08-01 00:00:00.000                                1
0002                                                         2006-08-03 00:00:00.000                                1
0002                                                         2006-08-04 00:00:00.000                                2（所影响的行数为 10 行）

Answer 3

to:libin_ftsafe 
结果不对,主要问题在日期中断后,没有重新生成序号to:i9988
结果正确,但对下面的语句是如何执行的不太明白,是否可以讲解或指点相关资料,
其作用是否与用游标逐行一样
update #t
   set @days=case when @item_no=item_no and dt=dateadd(day,1,@dt) then @days+1 else 1 end,
       @item_no=item_no,@dt=dt,days=@days

Answer 4

由于同一个item_no可能出现多次断号，一个sql语句写不出来，1楼的错了

Answer 5

这个用法是在论坛学的，所以讲解是讲解不清楚的说说个人理解：
update语句内部操作也是逐条进行的，总有执行的先后，这里用变量来保存了上一条的item_no和dt，用于这一条的比较，那个case语句就是计算这一条的days的，如果
item_no和上一条相同(@item_no=item_no) 而且 dt是上一条的下一天(dt=dateadd(day,1,@dt))，表示连续，那days就是上一条的days+1，否则不连续，就是1了其他的不讲了，不知道能否说清楚

Answer 6

select item_no,sl,dt,price,invoice,identity(int) nid into #t
 from t_tran a order by item_no, dtselect max(nid) nid,item_no,dt,gid into # 
from (select nid, item_no,dt,gid=case when (select count(1) from #t b where datediff(dd,b.dt,a.dt)=1 and b.item_no=a.item_no)=0 then nid else (select max(nid) from #t b where b.nid<a.nid and b.item_no=a.item_no and 
(select count(1) from #t c where datediff(dd,c.dt,b.dt)=1)=0 and b.item_no=a.item_no) end from #t a) x
group by item_no,dt,gid
select item_no,dt,(select count(1) from # b where b.nid<=a.nid and b.gid=a.gid) from # a
drop table t_tran,#t,#
/*
0001 2006-08-01 00:00:00.000 1
0001 2006-08-02 00:00:00.000 2
0001 2006-08-06 00:00:00.000 1
0001 2006-08-07 00:00:00.000 2
0001 2006-08-08 00:00:00.000 3
0001 2006-08-09 00:00:00.000 4
0001 2006-08-10 00:00:00.000 5
0002 2006-08-01 00:00:00.000 1
0002 2006-08-03 00:00:00.000 1
0002 2006-08-04 00:00:00.000 2
*/

Answer 7

只要SQL语句可以够长,我的写法就可以改成一条语句的,不用循环.当然,这没有必要.看贴时发现冒牌已写了那种方法,所以还是按临时表的用法来写.

Answer 8

楼主的表中本就有标识列。
我用临时表生成标识列只是为了写语句方便。 否则写条件时要写一堆很烦琐.因为有了原表中的标识列，所以那个临时表生成的标识列我也可以用count算法算出来.至于递规查询变量，通常下用的select查询较多一点，update查询也可以用.
刚才有个贴子里问到了，刚好有人写到了，你看一下就明白了。
http://community.csdn.net/Expert/topic/4965/4965240.xml?temp=.3989527

Answer 9

好久不写SQL句了,今天写一个,
如果同一item_no每天有多条记录,用这个:
select a.item_no,a.dt,max(datediff(dd,b.dt,a.dt)+1)
from t_tran a,t_tran b
where a.dt>=b.dt 
and a.item_no=b.item_no
and not exists (select 1 from t_tran where item_no=a.item_no and dt+1 between b.dt and a.dt and dt+1 not in (select dt  from t_tran where item_no=a.item_no))
group by a.item_no,a.dt
order by a.item_no,a.dt 如果同一item_no每天只有一条记录,用这个:select item_no,dt,datediff(dd,(select min(dt) from t_tran b where item_no=a.item_no and dt<=a.dt and not exists (select 1 from t_tran where item_no=a.item_no and dt+1 between b.dt and a.dt and dt+1 not in (select dt from t_tran where item_no=a.item_no))),dt)+1 
from t_tran a
order by a.item_no,a.dt ---逻辑:
not exists (select 1 from t_tran where item_no=a.item_no and dt+1 between b.dt and a.dt and dt+1 not in (select dt from t_tran where item_no=a.item_no)))

Answer 10

--这样也可以
select distinct item_no,dt,
datediff(day,(select max(dt) from (select item_no,dt from t_tran A where not exists(select 1 from t_tran where item_no=A.item_no and datediff(day,dt,A.dt)=1)) B where B.item_no=A.item_no and B.dt<=A.dt),dt)+1 连续天数
from t_tran A
order by item_no,dt

Answer 11

是的，我写复杂了。
以前写的那些因为每条记录中的某些数据要用到，所以要那样写。而这个只是个count，所以可以用楼上各位的写法。

Answer 12

zsforever(虎虎) 确实写得好!  我再写一个,不过效率可能跟虎差不了多少.select item_no,dt,
datediff(day,(select max(dt) from t_tran b where item_no=a.item_no and dt<=a.dt and not exists(select 1 from t_tran where item_no=b.item_no and datediff(day,dt,b.dt)=1 and dt<=b.dt)),dt)+1
from t_tran a
order by item_no,dt

Answer 13

select max(nid) from #t b where b.nid<a.nid and b.item_no=a.item_no and 
(select count(1) from #t c where datediff(dd,c.dt,b.dt)=1)=0 and b.item_no=a.item_noselect max(dt) from t_tran b where item_no=a.item_no and dt<=a.dt and not exists(select 1 from t_tran where item_no=b.item_no and datediff(day,dt,b.dt)=1 and dt<=b.dt)
其实思路都是一样的.我是懒得想,从以前写的一些先进先出的查询直接改过来的,因为那里面每行要计算数值，所以要生成分组ID。

Answer 14

这里效率最高的应该是libin_ftsafe(子陌红尘：当libin告别ftsafe)的，不过错错了几个别名
我修改一下
select 
A.item_no,A.dt, datediff(day,max(B.dt),A.dt)+1 连续天数
from 
  t_tran A,
  (select 
distinct item_no,dt 
  from 
t_tran A 
  where 
not exists(select 1 from t_tran where item_no=A.item_no and datediff(day,dt,A.dt)=1)) B
where 
A.item_no=B.item_no and A.dt>=B.dt
group by 
A.item_no,A.dt
order by 
A.item_no,A.dt

Answer 15

本公司为推广宣传公司产品，诚聘网络推广员。条件不限，只要家中有电脑，或者上班能上网的工作职员，会发邮件会利用QQ联系业务，宣传广告，信息发布，论坛发贴等方法宣传，有高额提成回报。可利用业余时间兼职，根据业绩每月最高可获报酬3000元左右。 详情请登陆：http://897288.com/?id=whui进入网站后可以点击网站上的“运作模式”，若有信心推广此网站，请联系每天8小时在线客服QQ：704757003。正式录用后赠送正版QQ好友群发软件和宣传资料。由于咨询者较多，请先看完网站内容后再做咨询，谢谢合作！