闷头把你的游标改成子查询,不知道对了吗。你试试 insert into @ID_List(id_bc) select t2.id_bc from tb_barcode t2, (select id_bc,dt_bc_scan,vr_bc_site,vr_bc_barcode from tb_barcode where vr_bc_site like @Site and dt_bc_scan >=@beginDate and dt_bc_scan <=@EndDate ) t1 where t2.id_bc in (select id_bc from t1) and t2.vr_bc_site = t1.vr_bc_site and t2.dt_bc_scan<t1.dt_bc_scan and substring(t2.vr_bc_barcode,5,4)>substring(t1.vr_bc_barcode ,5,4)
--共了15分钟研究你的语句,其实你的语句用下面这一条就够了.--条形码先进先出检查报表 CREATE PROCEDURE sp_bc_fifo_report @Site varchar(1000), @beginDate varchar(50), @endDate varchar(50) AS select a.vr_bc_site,a.vr_bc_onbr,a.vr_bc_part,a.vr_bc_barcode,a.dt_bc_scan from tb_barcode a join( select id_bc from tb_barcode a where dt_bc_scan between @beginDate and @EndDate and vr_bc_site like @Site and exists( select 1 from tb_barcode where vr_bc_site=a.vr_bc_site and dt_bc_scan<a.dt_bc_scan and substring(vr_bc_barcode,5,4) >substring(a.vr_bc_barcode,5,4) ) )b on a.id_bc=b.id_bc order by a.vr_bc_barcode go
个人觉得你的语句的不合理之处: 1.用游标循环,效率不理想2.查询条件语句:where vr_bc_site like @Site and dt_bc_scan >=@beginDate and dt_bc_scan <=@EndDate like会全表扫描,影响效率,所以应该换一下位置,改成这样: where dt_bc_scan between @beginDate and @EndDate and vr_bc_site like @Site3.用in是很慢的,所以改成join
http://expert.csdn.net/Expert/topic/2906/2906698.xml?temp=.2028772
insert into @ID_List(id_bc) select t2.id_bc
from tb_barcode t2,
(select id_bc,dt_bc_scan,vr_bc_site,vr_bc_barcode
from tb_barcode
where vr_bc_site like @Site and dt_bc_scan >=@beginDate and dt_bc_scan <=@EndDate
) t1
where t2.id_bc in (select id_bc from t1)
and t2.vr_bc_site = t1.vr_bc_site
and t2.dt_bc_scan<t1.dt_bc_scan
and substring(t2.vr_bc_barcode,5,4)>substring(t1.vr_bc_barcode ,5,4)
CREATE PROCEDURE sp_bc_fifo_report
@Site varchar(1000),
@beginDate varchar(50),
@endDate varchar(50)
AS
select a.vr_bc_site,a.vr_bc_onbr,a.vr_bc_part,a.vr_bc_barcode,a.dt_bc_scan
from tb_barcode a join(
select id_bc
from tb_barcode a
where dt_bc_scan between @beginDate and @EndDate
and vr_bc_site like @Site
and exists(
select 1 from tb_barcode
where vr_bc_site=a.vr_bc_site
and dt_bc_scan<a.dt_bc_scan
and substring(vr_bc_barcode,5,4)
>substring(a.vr_bc_barcode,5,4)
)
)b on a.id_bc=b.id_bc
order by a.vr_bc_barcode
go
1.用游标循环,效率不理想2.查询条件语句:where vr_bc_site like @Site and dt_bc_scan >=@beginDate and dt_bc_scan <=@EndDate
like会全表扫描,影响效率,所以应该换一下位置,改成这样:
where dt_bc_scan between @beginDate and @EndDate
and vr_bc_site like @Site3.用in是很慢的,所以改成join
dt_bc_scan ,id_bc,vr_bc_site