0

我的帖子

个人中心

设置

  发新话题
为什么条件 3,4行条件相同还能匹配       条件  ==>>>>  CHARINDEX('-',JOB_NO)=0

select Item_No,
case when JOB_NO like '安全库存%' then '安全库存' else JOB_NO end,
case when CHARINDEX('-',JOB_NO)=0 then left(JOB_NO,len(JOB_NO)-4) else JOB_NO end,
case when CHARINDEX('-',JOB_NO)=0 then cast(right(RTRIM(JOB_NO),2) as int) else 0 end
from PODETAIL where Po_No=@PO_NO and JOB_NO>'' order by Item_No,LEN(JOB_NO),JOB_NO


**********************************************************
以下是整个程序的完整代码


USE [HM]
GO
/****** Object:  StoredProcedure [dbo].[ZP_PO_PRT]    Script Date: 2018/6/26 19:08:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ZP_PO_PRT]
@PO_NO varchar(50)
AS
--declare @PO_NO varchar(50)='HM-B170623006'
declare @i int=1,@j int,@job_no varchar(50),@job_no1 varchar(50),@job_no2 varchar(50),@p1 int,@p2 int,@p3 int,@item_no varchar(50),@r varchar(50),@rr varchar(500)
declare @ii int,@jj int
declare @por table(id int identity,Po_No varchar(50),Item_No varchar(50),QTY decimal(18,4),Unit_Price decimal(18,5),AMT decimal(18,5),MRP_DATE datetime,JOB_NO varchar(500),remark varchar(500))
declare @po table(id int identity,Item_No varchar(50),JOB_NO varchar(50),JOB_NO1 varchar(50),P1 int)
insert into @por(Po_No,Item_No,QTY,Unit_Price,AMT,MRP_DATE,remark)
select @PO_NO,Item_No,SUM(Qty),AVG(Unit_Price),SUM(Qty*Unit_Price),MAX(MRP_DATE),MAX(remark) from PODETAIL where [email=Po_No=@PO_NO]Po_No=@PO_NO[/email] group by Item_No
insert into @po(Item_No,JOB_NO,JOB_NO1,P1)
select Item_No,
case when JOB_NO like '安全库存%' then '安全库存' else JOB_NO end,
case when CHARINDEX('-',JOB_NO)=0 then left(JOB_NO,len(JOB_NO)-4) else JOB_NO end,
case when CHARINDEX('-',JOB_NO)=0 then cast(right(RTRIM(JOB_NO),2) as int) else 0 end
from PODETAIL where [email=Po_No=@PO_NO]Po_No=@PO_NO[/email] and JOB_NO>'' order by Item_No,LEN(JOB_NO),JOB_NO
select @j=MAX(id) from @por
while @i<[email==@j]=@j[/email]
begin
set @rr=''
set @job_no2=''
set @p2=-1
set @p3=0 --记录连续数
select @item_no=Item_No from @por where [email=id=@i]id=@i[/email]
select @ii=MIN(id),@jj=MAX(id) from @po where [email=Item_No=@item_no]Item_No=@item_no[/email]
while @ii<[email==@jj]=@jj[/email]
begin
  select @p1=P1,@job_no=JOB_NO,@job_no1=JOB_NO1 from @po where [email=Item_No=@item_no]Item_No=@item_no[/email] and [email=id=@ii]id=@ii[/email]
  if @job_no1=@job_no2
  begin
   if @p2+1<>@p1 and @p2<>@p1
   begin
    set @rr=@rr+case when right(@rr,1)=',' then '' else ',' end + right(rtrim(@job_no),2)
    set @p3=0
   end
   else if @p2+1=@p1
   begin
    set @p3=@p3+1
    if @p3=1
    begin
     if right(@rr,1)=',' set @rr=left(@rr,len(@rr)-1)
    end
    else set @rr=left(@rr,len(@rr)-len(right(rtrim(@job_no),2))-1)
    set @rr=@rr+'-'+right(rtrim(@job_no),2)
   end
   set @p2=@p1
  end
  else
  begin
   if @rr>'' if right(@rr,1)=',' set @rr=left(@rr,len(@rr)-1)
   if @rr='' set @rr=@rr+@job_no+',' else set @rr=@rr+';'+@job_no
   set @job_no2=@job_no1
   set @p2=-1
   set @p3=0
  end
  set @p2=@p1
  set @ii=@ii+1
end
set @i=@i+1
if right(@rr,1)='-' or right(@rr,1)=',' set @rr=left(@rr,len(@rr)-1)
update @por set [email=JOB_NO=@rr]JOB_NO=@rr[/email] where [email=Item_No=@item_no]Item_No=@item_no[/email]
end
select t.*,ITEMMSTR.ITEM_DESCC,ITEMVENDOR.YOURMODEL,UNIT.UNIT_DESCC
from @por t
inner join POHEAD with(nolock) on t.Po_No=POHEAD.Po_No
left join ITEMVENDOR with(nolock) on POHEAD.Vdr_Code=ITEMVENDOR.VDR_CODE and t.Item_No=ITEMVENDOR.ITEM_NO
left join ITEMMSTR with(nolock) on t.Item_No=ITEMMSTR.ITEM_NO
left join UNIT with(nolock) on ITEMMSTR.UNIT=UNIT.UNIT



你是不同列,当然会执行的



case when,其实就是if else啊,你给条件,他就会判断的



我的51CTO博客

SQL Server MCITP, MVP, MCT, DBA
‹‹ 上一贴:关于sql怎么把一行中的列值合并查询结果放在该行中的 ...   |   下一贴:MySQL千万级数据量大表如何update问题 ››
  发新话题
快速回复主题
关于我们 | 诚聘英才 | 联系我们 | 网站大事 | 友情链接 |意见反馈 | 网站地图
Copyright©2005-2018 51CTO.COM
本论坛言论纯属发布者个人意见,不代表51CTO网站立场!如有疑义,请与管理员联系:bbs@51cto.com