22,209
社区成员
发帖
与我相关
我的任务
分享
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_QuerySalesTree --@cCoding='S000004'
@cCoding varchar(15)=''
as
select cSCQCoding, cSCode=year(dSCDate), cSName=cCoGarCode+'('+cCusGarCode+')' into #temp
from tm_SalesConfirmation a,(select cSCCusCode from tm_SalesConfirmation where cSCQCoding=@cCoding) b
where a.cSCCusCode=b.cSCCusCode and a.isctype<>-1
select IDENTITY(int,1,1) as cSCQCoding,cSCode=year(dSCDate),cSName=year(dSCDate) into #temp1
from tm_SalesConfirmation a,(select cSCCusCode from tm_SalesConfirmation where cSCQCoding=@cCoding)b
where a.cSCCusCode=b.cSCCusCode and a.isctype<>-1 group by year(dSCDate)
insert into #temp(cSCQCoding,cSCode,cSName) select cSCQCoding,cSCode,cSName from #temp1
alter table #temp add cParent varchar(15),iGrade int,bEnd bit
declare @cSCode int
declare @cSCQCoding varchar(15)
declare @cSName varchar(30)
declare @iNum int
declare cur cursor for select cSCode from #temp1
open cur
FETCH NEXT FROM cur
INTO @cSCode
WHILE @@FETCH_STATUS = 0
BEGIN
set @iNum=1
declare cur1 cursor for select cSCQCoding,cSName from #temp where cSCode=@cSCode and cast(cSCode as varchar(10))<>cSName
open cur1
FETCH NEXT FROM cur1
INTO @cSCQCoding,@cSName
WHILE @@FETCH_STATUS = 0
BEGIN
update #temp set cSCode=cSCode*1000+@iNum where cSCQCoding=@cSCQCoding
set @iNum=@iNum+1
FETCH NEXT FROM cur1 iNTO @cSCQCoding,@cSName
END
close cur1
deallocate cur1
FETCH NEXT FROM cur iNTO @cSCode
END
close cur
deallocate cur
drop table #temp1
update #temp set iGrade=1,bEnd=0 where cSCode<3000 --更新语句
update a set iGrade=2,cParent=b.cSCQCoding,bEnd=1 from #temp a,#temp b
where a.cSCode>3000 and left(cast(a.cSCode as varchar(10)),4)=cast(b.cSCode as varchar(10)) --更新语句
select * from #temp order by cSCode
drop table #temp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--a表里没有iGrade这个列。