现有一牛基本信息表
结构是这样的
表名:niu
耳号 阶段 状态
erhao PHASE STATE
其中阶段是(隔离场,出栏),状态是(死,活)现要写一个sql得出,存栏明细=进入隔离场牛的明细-出栏明细-死亡明细现在单独写,进入隔离场牛的明细,出栏明细,死亡明细都能写出,可怎么把这三个sql组合成一个sql求助
结构是这样的
表名:niu
耳号 阶段 状态
erhao PHASE STATE
其中阶段是(隔离场,出栏),状态是(死,活)现要写一个sql得出,存栏明细=进入隔离场牛的明细-出栏明细-死亡明细现在单独写,进入隔离场牛的明细,出栏明细,死亡明细都能写出,可怎么把这三个sql组合成一个sql求助
select * from niu where phase = '隔离' and state = '活'不就可以了吗?
进入隔离场牛的明细
select * from Heniu as hn where hn.geliYmd<=:geliYmd and hn.phase>=:'隔离' 出栏明细
select * from Heniu as hn where hn.chulanYmd<=:chulanYmd and hn.phase=:'出栏'死亡明细
select * from Heniu as hn where hn.dieYmd<=:dieYmd and hn.phase>=:'隔离' and hn.state=:'死'geliYmd,chulanYmd,dieYmd是前台输入的查询日期,是一个日期比如都是(20070609)
就是要把这三个语句和成一个sql
where not exists
(
select * from niu n2
where n.erhao=n2.erhao
and (
n2.STATE='死'
or n2.PHASE='出栏'
)
)
select * from Heniu as hn where hn.geliYmd<=:geliYmd and hn.phase>=:'隔离' 出栏明细
select * from Heniu as hn where hn.chulanYmd<=:chulanYmd and hn.phase=:'出栏'死亡明细
select * from Heniu as hn where hn.dieYmd<=:dieYmd and hn.phase>=:'隔离' and hn.state=:'死'
-----用exists方法
select * from Heniu as hn where hn.geliYmd<=:geliYmd and hn.phase>=:'隔离'
and not exists(select 1 from Heniu as A where hn.chulanYmd<=:chulanYmd and hn.phase=:'出栏' and A.erhao=hn.erhao )
and not exists(select 1 from Heniu as B where hn.dieYmd<=:dieYmd and hn.phase>=:'隔离' and hn.state=:'死'
and B.erhao=hn.erhao)