select * from pig

　ear_num son_num embryo_num
　------------------------------------------
　　1　　　　14　　　　1
　　1　　　　15　　　　2
　　1　　　　15　　　　3
　　2　　　　16　　　　1
　　2　　　　17　　　　2
　　2　　　　18　　　　3

问题描述，如：要得到所有连续三胎产仔数目都小于21的母猪资料（例如耳号为1的母猪，其第一胎产仔数为14，第二胎为15，第三胎也是15，那末就符合条件）

----------------------------------------------------------------------------------------------

--我的答案：
--select * from pig order by ear_num,embryo_num
if exists (select * from tempdb..sysobjects where name like '#tmp%')
drop table #tmp
go
if exists (select * from tempdb..sysobjects where name like '#zzx%')
drop table #zzx
go
select * into #zzx from pig where son_num <21 --建产仔数目都小于21的临时表
--select * from #zzx order by ear_num,embryo_num
select * into #tmp from pig where 1=2 --临时表用于存放满足连续三胎产仔数目都小于21的猪的起初胎次。
--,sum int
if (select count(*) from #zzx)>3
--begin
declare @num int --存放最小的猪耳号变量
select @num=(select min(ear_num) from #zzx)
declare @time int --存放猪耳号最小且胎次最小的变量
set @time=(select min(embryo_num) from #zzx where ear_num=@num)
--select @time
while @time<=(select max(embryo_num) from #zzx where ear_num =@num) --or @num <(select max(ear_num) from #zzx)
begin--内while
if (select count(*) from #zzx where embryo_num in (select embryo_num from #zzx where embryo_num =@time+1 or embryo_num =@time+2)
and ear_num =@num)=2
begin --内if
insert #tmp(ear_num,son_num,embryo_num)
select ear_num,son_num,embryo_num from #zzx where embryo_num=@time and ear_num=@num
delete #zzx where embryo_num=@time and ear_num=@num
select @num=(select min(ear_num) from #zzx)
set @time=(select min(embryo_num) from #zzx where ear_num=@num)
--select * from #tmp
end --内if
else
begin
delete #zzx where embryo_num=@time and ear_num=@num
select @num=(select min(ear_num) from #zzx)
set @time=(select min(embryo_num) from #zzx where ear_num=@num)
end
end--内while
select * from #tmp
go

----------------------------------------------------------------------------------------------

--另一个朋友的答案
select t1.* from pig t1,
(select p1.ear_num earnum,p1.embryo_num num1,p2.embryo_num num2,p3.embryo_num num3 from pig p1,pig p2,pig p3
where p1.ear_num=p2.ear_num and p2.ear_num=p3.ear_num
and p1.son_num<21 and p2.son_num<21 and p3.son_num<21
and p2.embryo_num=p1.embryo_num+1 and p3.embryo_num=p2.embryo_num+1
) t2
where t1.ear_num=t2.earnum and (t1.embryo_num=t2.num1 or t1.embryo_num=t2.num2 or t1.embryo_num=t2.num3)