请教一个SQL语句怎么写？

xmen

我要为朋友写个小程序管理母猪育种的过程
其中有个查询是要了解连续三胎每胎的产仔数都小于21的母猪档案，该表中有胎次的字段embryo_num,每胎产仔数num_spig,以及一些其他字段，如母猪耳号enum_fpig, 生产日期birth_date等字段。表名是BIRTH_FPIG.
我本来想用ORACLE建库，但是觉得太小题大做，所以就用ACCESS建了库。 当然也可以用SQLSERVER来建库。
该如何来写这个查询语句呢？很急！
请各位大侠赐教！多谢!

magicangel

select * from BIRTH_FPIG where embryo_num=3 and num_spig<21 and enum_fpig=\'具体母猪耳号\' and convert(char(10),birth_date,121) between \'日期（yyyy-mm-dd）\' and \'日期（yyyy-mm-dd）\'
是这个意思吗。

xmen

（1）我的问题的SQL脚本和样例数据，如：
create table pig(
ear_ num number(5) not null, --母猪耳号
son_num number(3) , -- 每胎产仔数
embryo_num number(5), -- 胎次
);
insert into pig values(\'1\',\'14\',\'1\');
insert into pig values(\'1\',\'15\',\'2\');
insert into pig values(\'1\',\'15\',\'3\');
insert into pig values(\'2\',\'16\',\'1\');
insert into pig values(\'2\',\'17\',\'2\');
insert into pig values(\'2\',\'18\',\'3\');


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

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

（3）期望结果，如：
　EAR_NUM SON_NUM EMBRYO_NUM
------------------ ----------------- --------------------------
1 14 1
1 15 2
1 15 3
2 16 1
2 17 2
2 18 3

请大侠帮忙！

magicangel

--select * from pig order by ear_num,embryo_num
drop table #tmp
drop table #zzx


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
--select * from #zzx

/*结果解释
如数据为：
1        15        2
1        15        3
1        30        4
1        15        5
1        50        6
1        10        7
1        15        8
1        10        9
1        10        17
1        15        18
1        10        19
2        16        1
2        17        2
2        18        3
2        66        4
2        17        5
2        48        6
2        16        7
2        17        8
2        18        9
2        16        17
2        17        18
2        18        19
结果为
1        10        7
1        10        17
2        16        1
2        16        7
2        16        17
第一组数据这样解释,起初胎次为7表明耳号为1且胎次为7，8，9这组满足要求*/

--sqlserver2000里验证通过。

magicangel

呵呵，希望这次没有理解错。

eru

不用这么麻烦，一条sql搞定：

select * from pig where ear_num in
(select p1.ear_num 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
)

magicangel

TO eru
在sqlserver2000中验证不通过。
不只你试过没有，你的语句的效果跟
select * from pig
取得的结果一样。
自连接没办法做到。只能用临时表，象我那样，或用游标。
游标我个人不到万不得以不用，执行效率太低，方便了自己，麻烦了机器，特别在大型应用中特别突出。在复旦金仕达的时候，我就花了几个月的时间重整了公司his系统的出入院病区系统，游标用多是导致病人出院结帐等待时间长的一个重要原因。

eru

我试过，我这条语句取出的是曾经有连续三次孩子数<21的母猪的所有生孩子的记录，在你的数据里结果和select * from pig一样是因为所有的母猪都有连续三次孩子数<21的情况发生，如果你一定只要那几个月的记录那么可以这样写：

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)

magicangel

佩服啊，eru，居然有这么简洁的select语句。你那样的语句我就写不出，推荐楼主相互对照。我的也是通过的，主要是体现临时表的优势，执行成本较低。哎，看来自己还得修炼。向你学习。
现对你的语句加上order by，这样会明了一点。

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)
order by t1.ear_num,t1.embryo_num

xmen

感激不尽！
多谢了！