INSERT INTO #Oldfencemytable (oldfence_name, animal_id) SELECT OldFence_Name, Animal_Id FROM TBL_AnimalMove WHERE (Animal_Id IN (SELECT animal_id FROM #mytable)) AND (OpTime IS NOT NULL) AND (OpTime IN (SELECT MIN(optime) FROM tbl_animalmove WHERE animal_id IN (SELECT animal_id FROM #mytable) AND optime IS NOT NULL GROUP BY animal_id))
这个是存储过程里的一条SQL语句,没有在其他地方用过#Oldfencemytable 表,但是注释这条语句跟不注释得到的查询返回数据不一样,不知道怎么回事
这个是存储过程里的一条SQL语句,没有在其他地方用过#Oldfencemytable 表,但是注释这条语句跟不注释得到的查询返回数据不一样,不知道怎么回事
结构我把它注释和不注释会得到不一样的结果
/*
--查询某一日期动物的存栏情况,
*/
CREATE proc GetStartMonthNumber @zoo_name nvarchar(20),@branch_name nvarchar(20),@dept_name nvarchar(20),@Parmfence_name nvarchar(20), @startTime datetime
as
begin
declare @rows int
declare @Linfence_name nvarchar(25)
declare @minTime datetime
declare @fence_name nvarchar(25)
declare @LinFence_id int
declare @Linanimalgroup_id int
declare @LinAnimal_id nvarchar(20)
declare @LinAnimalgroup_name nvarchar(20)--查询自己殖的3个以外的和引进的动物数目
create table #mytable
(
id int identity(1,1),
fence_id int,
animalgroup_id int,
animal_id nvarchar(20),
--fence_name nvarchar(25)
)
create table #Tempmytable
(
id int identity(1,1),
fence_name nvarchar(25),
animalgroup_name nvarchar(25),
animal_id nvarchar(20)
)
create table #Oldfencemytable
(
id int identity(1,1),
Oldfence_name nvarchar(25),
animal_id nvarchar(20),
Oldfence_id int
)
--查询最原始的栏馆舍 05-10delete from #Oldfencemytableinsert into #mytable(animal_id,animalgroup_id,fence_id) select animal_id,animalgroup_id,fence_id from tbl_animalinfo where ((dateadd(month,3,borndate)<@StartTime and cometime is null) or cometime <@startTime ) --update #mytable set fence_name =(select fence_name from tbl_fenceinfo where #mytable.fence_id = tbl_fenceinfo.fence_id )
delete from ceshimytable
insert into ceshimytable(animal_id,animalgroup_id,fence_id) select animal_id,animalgroup_id,fence_id from #mytable
--注释下面一行跟不注释下面一行产生的结果不同
INSERT INTO #Oldfencemytable (oldfence_name, animal_id) SELECT OldFence_Name, Animal_Id FROM TBL_AnimalMove WHERE (Animal_Id IN (SELECT animal_id FROM #mytable)) AND (OpTime IS NOT NULL) AND (OpTime IN (SELECT MIN(optime) FROM tbl_animalmove WHERE animal_id IN (SELECT animal_id FROM #mytable) AND optime IS NOT NULL GROUP BY animal_id))
/*
update #Oldfencemytable set Oldfence_id = (select fence_id from tbl_fenceinfo where #Oldfencemytable.Oldfence_name =tbl_fenceinfo.fence_name )
delete from Oldfencemytable
insert into Oldfencemytable(animal_id,Oldfence_name,Oldfence_id) select animal_id,Oldfence_name,Oldfence_id from #Oldfencemytable
*//*
DELETE FROM #mytable WHERE (animal_id IN
(SELECT #mytable.animal_id
FROM #Oldfencemytable, #mytable
WHERE #Oldfencemytable.animal_id = #mytable.animal_id AND
#Oldfencemytable.oldfence_id <> #mytable.fence_id))
--05-10
*/set @rows=@@rowcount
delete from tbl_startwhile @rows>0
begin
set @linanimalgroup_id=0
set @linFence_id=0
set @linFence_name=''
set @linAnimalgroup_name=''
set @linanimal_id=''
select @linFence_id=fence_id,@linanimalgroup_id=animalgroup_id,@linanimal_id=animal_id from #mytable where id=@rows
select @linFence_name=fence_name from tbl_fenceinfo where fence_id=@linFence_id
select @linAnimalgroup_name=animal_name from tbl_animalgroupInfo where animalgroup_id=@linanimalgroup_id
--查询动物是否已经死亡或者淘汰
if exists(select id from tbl_animaldead where DeadDate<@startTime and animal_id=@linanimal_id)--继续下一次循环
begin
print 'Dead'
set @rows=@rows-1
continue
end
--查询动物是否已经出售
if exists(select id from TBL_Animaloutinfo where outtime<@startTime and animal_id=@linanimal_id)--
beginset @rows=@rows-1
continue
end
--查询动物是否有调栏的记录
print'A'
if exists(select id from TBL_AnimalMove where optime>@startTime and animal_id=@linanimal_id)
begin
--查询出最小日期的调栏记录的原栏舍
print'move'
print @LinAnimal_id
--insert into #Tempmytable(fence_name,animalgroup_name,animal_id) values (@Linfence_name,@linAnimalgroup_name,@linanimal_id)
--select @mintime=min(optime) , @Linfence_name=oldFence_name from tbl_animalmove where animal_id=@LinAnimal_id and OpTime>@startTime group by oldFence_name
--05-10注释前一行,加上后一行,作用:根据ID找到最早日期的调栏记录
select top 1 @mintime= optime, @fence_name=oldFence_name from tbl_animalmove where animal_id=@LinAnimal_id and OpTime>@startTime order by optime asc
end
insert into #Tempmytable(fence_name,animalgroup_name,animal_id) values(@Linfence_name,@linAnimalgroup_name,@linanimal_id)set @rows=@rows-1
end--循环退出
insert into tbl_start(animalgroup_name,fence_name,startNumber)
select animalgroup_name,fence_name,count(*) from
(select m.*,n.zoo_name from (
(select e.*,f.branch_name,f.zoo_id from (
(select c.*,d.dept_name,d.branch_id from (
( select a.*,b.dept_id from (
(select Animalgroup_name,fence_name from #Tempmytable ) as a left join
(select fence_name,dept_id from tbl_fenceinfo ) as b on a.fence_name=b.fence_name)) as c left join
(select dept_id,dept_name,branch_id from tbl_deptinfo) as d on c.dept_id=d.dept_id)) as e left join
(select branch_id,branch_name,zoo_id from tbl_branchinfo ) as f on e.branch_id=f.branch_id)) as m left join
(select zoo_id,zoo_name from tbl_zooinfo ) as n on m.zoo_id=n.zoo_id) where zoo_name like @zoo_name and branch_name like @branch_name and dept_name like @dept_name and fence_name like @Parmfence_name) as s group by Animalgroup_name,fence_name--select * from #Tempmytable
--select * from #Tempmytable where fence_name='大天鹅湖' and animalgroup_name='黑天鹅'
--select * from #Tempmytable where fence_name ='亚洲森林' and animalgroup_name ='马鹿'
drop table #Tempmytable
drop table #mytableend
GO整个存储过程发一下,有热心的朋友帮忙看看,可能这个问题我也没说清楚
delete from tbl_startwhile @rows>0
begin
set @linanimalgroup_id=0
set @linFence_id=0
set @linFence_name=''
set @linAnimalgroup_name=''
set @linanimal_id=''
select @linFence_id=fence_id,@linanimalgroup_id=animalgroup_id,@linanimal_id=animal_id from #mytable where id=@rows
select @linFence_name=fence_name from tbl_fenceinfo where fence_id=@linFence_id
select @linAnimalgroup_name=animal_name from tbl_animalgroupInfo where animalgroup_id=@linanimalgroup_id
关键在这里
set @rows=@@rowcount
delete from tbl_startwhile @rows>0如果不注释, @rows可能会大于0 ,则会执行到while语句块
注释了则@rows==0,则不会执行到
set @rows=@@rowcount
delete from tbl_startwhile @rows>0还说没用到,你看看,你的insert语句影响到了@@rowcount,你有引用了@@rowcount
当然结果不同了