我在一个存储过程中循环客户编号,对每个客户的数据进行处理,处理过程中使用了临时表,处理完之后再把它插入到一张总的临时表中,等处理完所有的客户数据后,我再从总的临时表里查询统计数据。
这个思路是没有问题的,但是执行的结果会出现一种奇怪的现象,每一次查询出来的结果有时候不准确,有时候准确。
按道理来说,执行相同的sql代码,查询出来的结果应该都是一样的,为什么我查询出来的结果会不一致呢?
这个问题困扰了我很久了,希望各位帮忙解决一下。
这个思路是没有问题的,但是执行的结果会出现一种奇怪的现象,每一次查询出来的结果有时候不准确,有时候准确。
按道理来说,执行相同的sql代码,查询出来的结果应该都是一样的,为什么我查询出来的结果会不一致呢?
这个问题困扰了我很久了,希望各位帮忙解决一下。
create table #tempWaiFuData(
[ShopCode] nvarchar(50) ,
[Class] nvarchar(20) ,
[ProdID] int ,
[ProdCode] char(20) ,
[ProdName] nvarchar(50) ,
[Reserve2] nvarchar(20) ,
[UnitPrice] numeric(8, 2) ,
[etband] nvarchar(50) ,
[Series] nvarchar(20) ,
[Sex] nvarchar(20) ,
[RecommendType] nvarchar(20) ,
[Category] nvarchar(20) ,
[Part] nvarchar(20) ,
[Material] nvarchar(100) ,
[Barcode] nvarchar(50) ,
[Designer] nvarchar(50) ,
[SizeNumber] int ,
[Discount] numeric(4, 2) ,
[SizePosition] nvarchar(20) ,
[指订] int ,
[自订] int ,
[合计] int ,
[指订金额] numeric(18, 2) ,
[自订金额] numeric(18, 2) ,
[合计金额] numeric(18, 2)
)
if object_ID('tempdb.dbo.#tempproduct') is not null drop table #tempproduct
select bp.Class,bp.ProdID,bp.ProdCode,bp.ProdName,bp.Reserve2,bp.UnitPrice,bp.etband,
bp.Series,bp.Sex,bp.RecommendType,bp.Category,bp.Part,bp.Material,bp.Barcode,bp.Designer,bp.Pattern, bps.Size1 as S1
,bps.Size2 as S2,bps.Size3 as S3,bps.Size4 as S4,bps.Size5 as S5,bps.Size6 as S6,bps.Size7 as S7
,bps.Size8 as S8,bps.SizeNumber,bp.ColorCode into #tempproduct
from BaseProduct bp inner join
BaseProductSize bps on bps.SizeClass=bp.SizeClass
where bp.PriceBand ='外服'
order by bp.ProdCode,bp.ColorCode,bp.Pattern
if object_ID('tempdb.dbo.#tempc') is not null drop table #tempc
select SHOP into #tempc from v_Shop_Dealer_Agent_Region_Head where AGENT=@AgentCode
alter table #tempc add id int identity(1,1)
declare @nowkehu int set @nowkehu=1
declare @rowcountkehu int select @rowcountkehu=COUNT(*) from #tempc
declare @ClientCode nvarchar(50)
while @nowkehu<=@rowcountkehu
begin
select @ClientCode=SHOP from #tempc where id=@nowkehu
--exec P_GetOrderofWaiFu3 @ClientCode
--print @ClientCode
declare @DealerCode nvarchar(50)
select @DealerCode=b.ClientRe
from BaseClient a inner join
BaseClient b on a.ParentCode=b.ClientCode
where a.ClientCode=@ClientCode
if object_ID('tempdb.dbo.#tempod') is not null drop table #tempod
select od.OrderNO,bp.ProdCode,bp.ColorCode,
(select top 1 pattern from Baseproduct where ProdCode=bp.ProdCode and ColorCode=bp.ColorCode order by Pattern asc) Pattern,
od.Size1,od.Size2,od.Size3,od.Size4
,od.Size5,od.Size6,od.Size7,od.Size8,
isnull((select top 1 Discount from OrderSuggest
where ClientCode=@DealerCode and ProdCode=od.ProdCode),1) as Discount into #tempod
from OrderDetail od inner join
Baseproduct bp on bp.prodcode=od.Prodcode and bp.colorCode=od.colorCode and bp.pattern=od.pattern
where od.OrderNO=@ClientCode
and od.IsDeleted=0 and bp.ColorCode in ('指订','自订')
Order by bp.ProdCode,bp.ColorCode,Pattern
if (select COUNT(*) from #tempod)>0
begin
if object_ID('tempdb.dbo.#temp') is not null drop table #temp
select @ClientCode as ShopCode,bp.Class,bp.ProdID,bp.ProdCode,bp.ProdName,bp.Reserve2,bp.UnitPrice,bp.etband,
bp.Series,bp.Sex,bp.RecommendType,bp.Category,bp.Part,bp.Material,bp.Barcode,bp.Designer,bp.Pattern,bp.ColorCode,
od.Size1,od.Size2,od.Size3,od.Size4
,od.Size5,od.Size6,od.Size7,od.Size8, S1
,S2,S3,S4, S5, S6, S7
,S8,SizeNumber,od.Discount into #temp
from #tempproduct bp left outer join #tempod od
on bp.ProdCode=od.ProdCode and bp.ColorCode=od.ColorCode and bp.Pattern=od.Pattern
--where isnull(od.orderno,@ClientCode)=@ClientCode
order by bp.ProdCode,bp.ColorCode,bp.Pattern asc
drop table #tempod
--select * from #temp
alter table #temp add Qty int
alter table #temp add SizePosition nvarchar(10)
alter table #temp add id int identity(1,1)
declare @now int set @now=1
declare @rowcount int select @rowcount=count(id) from #temp
declare @SizeNumber int,@Discount numeric(5,2)
declare @S1 nvarchar(20),@S2 nvarchar(20),@S3 nvarchar(20),@S4 nvarchar(20)
,@S5 nvarchar(20),@S6 nvarchar(20),@S7 nvarchar(20),@S8 nvarchar(20),@ProdCode char(20) ,@ColorCode nvarchar(20)
declare @Size1 int,@Size2 int,@Size3 int,@Size4 int
,@Size5 int,@Size6 int,@Size7 int,@Size8 int
declare @SizeDesc nvarchar(20)
while @now<=@rowcount
begin
select @SizeDesc=Reserve2,@S1=S1,@S2=S2,@S3=S3,@S4=S4,@S5=S5,@S6=S6,@S7=S7,@S8=S8,@ProdCode=ProdCode,
@ColorCode=@ColorCode, @SizeNumber =SizeNumber,@Discount=Discount,
@Size1=Size1,@Size2=Size2,@Size3=Size3,@Size4=Size4,@Size5=Size5,@Size6=Size6,@Size7=Size7,@Size8=Size8
from #temp where id=@now
if exists (select ProdCode from #temp where id=@now and (isnull(Size1,0) +ISNULL( Size2,0)+ISNULL( Size3,0)+ISNULL( Size4,0)+ISNULL( Size5,0)+ISNULL( Size6,0)+ISNULL( Size7,0)+ISNULL( Size8,0))>0)
begin
if @SizeNumber=1
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
end
else if @SizeNumber=2
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
end
else if @SizeNumber=3
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
end
else if @SizeNumber=4
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
end
else if @SizeNumber=5
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
end
else if @SizeNumber=6
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
update #temp set Qty=@Size6,Discount=@Discount where id=@now+5
end
else if @SizeNumber=7
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
update #temp set Qty=@Size6,Discount=@Discount where id=@now+5
update #temp set Qty=@Size7,Discount=@Discount where id=@now+6
end
else if @SizeNumber=8
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
update #temp set Qty=@Size6,Discount=@Discount where id=@now+5
update #temp set Qty=@Size7,Discount=@Discount where id=@now+6
update #temp set Qty=@Size8,Discount=@Discount where id=@now+7
end
end
set @now=@now+@SizeNumber
end
[ShopCode] nvarchar(50) ,
[Class] nvarchar(20) ,
[ProdID] int ,
[ProdCode] char(20) ,
[ProdName] nvarchar(50) ,
[Reserve2] nvarchar(20) ,
[UnitPrice] numeric(8, 2) ,
[etband] nvarchar(50) ,
[Series] nvarchar(20) ,
[Sex] nvarchar(20) ,
[RecommendType] nvarchar(20) ,
[Category] nvarchar(20) ,
[Part] nvarchar(20) ,
[Material] nvarchar(100) ,
[Barcode] nvarchar(50) ,
[Designer] nvarchar(50) ,
[SizeNumber] int ,
[Discount] numeric(4, 2) ,
[SizePosition] nvarchar(20) ,
[指订] int ,
[自订] int ,
[合计] int ,
[指订金额] numeric(18, 2) ,
[自订金额] numeric(18, 2) ,
[合计金额] numeric(18, 2)
)
if object_ID('tempdb.dbo.#tempproduct') is not null drop table #tempproduct
select bp.Class,bp.ProdID,bp.ProdCode,bp.ProdName,bp.Reserve2,bp.UnitPrice,bp.etband,
bp.Series,bp.Sex,bp.RecommendType,bp.Category,bp.Part,bp.Material,bp.Barcode,bp.Designer,bp.Pattern, bps.Size1 as S1
,bps.Size2 as S2,bps.Size3 as S3,bps.Size4 as S4,bps.Size5 as S5,bps.Size6 as S6,bps.Size7 as S7
,bps.Size8 as S8,bps.SizeNumber,bp.ColorCode into #tempproduct
from BaseProduct bp inner join
BaseProductSize bps on bps.SizeClass=bp.SizeClass
where bp.PriceBand ='外服'
order by bp.ProdCode,bp.ColorCode,bp.Pattern
if object_ID('tempdb.dbo.#tempc') is not null drop table #tempc
select SHOP into #tempc from v_Shop_Dealer_Agent_Region_Head where AGENT=@AgentCode
alter table #tempc add id int identity(1,1)
declare @nowkehu int set @nowkehu=1
declare @rowcountkehu int select @rowcountkehu=COUNT(*) from #tempc
declare @ClientCode nvarchar(50)
while @nowkehu<=@rowcountkehu
begin
select @ClientCode=SHOP from #tempc where id=@nowkehu
--exec P_GetOrderofWaiFu3 @ClientCode
--print @ClientCode
declare @DealerCode nvarchar(50)
select @DealerCode=b.ClientRe
from BaseClient a inner join
BaseClient b on a.ParentCode=b.ClientCode
where a.ClientCode=@ClientCode
if object_ID('tempdb.dbo.#tempod') is not null drop table #tempod
select od.OrderNO,bp.ProdCode,bp.ColorCode,
(select top 1 pattern from Baseproduct where ProdCode=bp.ProdCode and ColorCode=bp.ColorCode order by Pattern asc) Pattern,
od.Size1,od.Size2,od.Size3,od.Size4
,od.Size5,od.Size6,od.Size7,od.Size8,
isnull((select top 1 Discount from OrderSuggest
where ClientCode=@DealerCode and ProdCode=od.ProdCode),1) as Discount into #tempod
from OrderDetail od inner join
Baseproduct bp on bp.prodcode=od.Prodcode and bp.colorCode=od.colorCode and bp.pattern=od.pattern
where od.OrderNO=@ClientCode
and od.IsDeleted=0 and bp.ColorCode in ('指订','自订')
Order by bp.ProdCode,bp.ColorCode,Pattern
if (select COUNT(*) from #tempod)>0
begin
if object_ID('tempdb.dbo.#temp') is not null drop table #temp
select @ClientCode as ShopCode,bp.Class,bp.ProdID,bp.ProdCode,bp.ProdName,bp.Reserve2,bp.UnitPrice,bp.etband,
bp.Series,bp.Sex,bp.RecommendType,bp.Category,bp.Part,bp.Material,bp.Barcode,bp.Designer,bp.Pattern,bp.ColorCode,
od.Size1,od.Size2,od.Size3,od.Size4
,od.Size5,od.Size6,od.Size7,od.Size8, S1
,S2,S3,S4, S5, S6, S7
,S8,SizeNumber,od.Discount into #temp
from #tempproduct bp left outer join #tempod od
on bp.ProdCode=od.ProdCode and bp.ColorCode=od.ColorCode and bp.Pattern=od.Pattern
--where isnull(od.orderno,@ClientCode)=@ClientCode
order by bp.ProdCode,bp.ColorCode,bp.Pattern asc
drop table #tempod
--select * from #temp
alter table #temp add Qty int
alter table #temp add SizePosition nvarchar(10)
alter table #temp add id int identity(1,1)
declare @now int set @now=1
declare @rowcount int select @rowcount=count(id) from #temp
declare @SizeNumber int,@Discount numeric(5,2)
declare @S1 nvarchar(20),@S2 nvarchar(20),@S3 nvarchar(20),@S4 nvarchar(20)
,@S5 nvarchar(20),@S6 nvarchar(20),@S7 nvarchar(20),@S8 nvarchar(20),@ProdCode char(20) ,@ColorCode nvarchar(20)
declare @Size1 int,@Size2 int,@Size3 int,@Size4 int
,@Size5 int,@Size6 int,@Size7 int,@Size8 int
declare @SizeDesc nvarchar(20)
while @now<=@rowcount
begin
select @SizeDesc=Reserve2,@S1=S1,@S2=S2,@S3=S3,@S4=S4,@S5=S5,@S6=S6,@S7=S7,@S8=S8,@ProdCode=ProdCode,
@ColorCode=@ColorCode, @SizeNumber =SizeNumber,@Discount=Discount,
@Size1=Size1,@Size2=Size2,@Size3=Size3,@Size4=Size4,@Size5=Size5,@Size6=Size6,@Size7=Size7,@Size8=Size8
from #temp where id=@now
if exists (select ProdCode from #temp where id=@now and (isnull(Size1,0) +ISNULL( Size2,0)+ISNULL( Size3,0)+ISNULL( Size4,0)+ISNULL( Size5,0)+ISNULL( Size6,0)+ISNULL( Size7,0)+ISNULL( Size8,0))>0)
begin
if @SizeNumber=1
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
end
else if @SizeNumber=2
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
end
else if @SizeNumber=3
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
end
else if @SizeNumber=4
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
end
else if @SizeNumber=5
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
end
else if @SizeNumber=6
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
update #temp set Qty=@Size6,Discount=@Discount where id=@now+5
end
else if @SizeNumber=7
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
update #temp set Qty=@Size6,Discount=@Discount where id=@now+5
update #temp set Qty=@Size7,Discount=@Discount where id=@now+6
end
else if @SizeNumber=8
begin
update #temp set Qty=@Size1,Discount=@Discount where id=@now
update #temp set Qty=@Size2,Discount=@Discount where id=@now+1
update #temp set Qty=@Size3,Discount=@Discount where id=@now+2
update #temp set Qty=@Size4,Discount=@Discount where id=@now+3
update #temp set Qty=@Size5,Discount=@Discount where id=@now+4
update #temp set Qty=@Size6,Discount=@Discount where id=@now+5
update #temp set Qty=@Size7,Discount=@Discount where id=@now+6
update #temp set Qty=@Size8,Discount=@Discount where id=@now+7
end
end
set @now=@now+@SizeNumber
end
select * into #ptemp
from #temp
order by ProdCode,ColorCode,Pattern
drop table #temp
alter table #ptemp drop column pattern
if object_ID('tempdb.dbo.#t1') is not null drop table #t1
SELECT * into #t1 FROM (select * from #ptemp) a PIVOT(sum(Qty) FOR ColorCode IN ([指订],[自订])) b
drop table #ptemp
alter table #t1 add [合计] numeric(7,0)
alter table #t1 add [指订金额] numeric(18,4)
alter table #t1 add [自订金额] numeric(18,4)
alter table #t1 add [合计金额] numeric(18,4)
update #t1 set [指订金额]=UnitPrice*[指订]*Discount,
[自订金额]=UnitPrice*[自订]*Discount,
[合计]=ISNULL([指订],0)+ISNULL([自订],0)
update #t1
set [合计金额]=isnull([指订金额],0)+isnull([自订金额],0)
insert into #tempWaiFuData
select * from #t1
--select @ClientCode,SUM(合计) ,COUNT(ProdCode) from #t1
drop table #t1
end
else
begin
drop table #tempod
end
set @nowkehu=@nowkehu+1
end
drop table #tempc drop table #tempproduct
select '' as ShopCode,Class,ProdID,ProdCode,ProdName,Reserve2,UnitPrice,etband,
Series,Sex,RecommendType,Category,Part,Material,Barcode,Designer,SizeNumber,1 as Discount,'' as SizePosition,
sum(指订) as 指订,sum(自订) as 自订,sum(合计) as 合计,sum(指订金额) as 指订金额,sum(自订金额) as 自订金额,
sum(合计金额) as 合计金额 from #tempWaiFuData
group by Class,ProdID,ProdCode,ProdName,Reserve2,UnitPrice,etband,
Series,Sex,RecommendType,Category,Part,Material,Barcode,Designer,SizeNumberdrop table #tempWaiFuData
给临时表加一个identity(1,1)的列,然后根据该列不大于临时表的行数,循环临时表中的每一行,每循环一次,循环因子加1