部分 检索条件
<<検査検収結果情報>>
PJ3600.契約種別 = ‘4’ ANDPJ3600.年度=PJ3500.年度 AND
PJ3600.案件番号 = PJ3500.案件番号 AND
PJ3600.所属課 = PJ3500.所属課 AND
PJ3600.契約種別 = PJ3500.契約種別 AND<<契約結果情報>>
変更契約時: ※変更契約情報存在的时候
PJ3500.年度=PJ3510.年度 AND
PJ3500.案件番号 = PJ3510.案件番号 AND
PJ3500.所属課 = PJ3510.所属課 AND
PJ3500.契約種別 = PJ3510.契約種別 AND
変更契約情報.変更回数=変更回数(MAX値)要检索的是如果PJ3510(変更契約情報)存在数据的话,则查询最大变更回数 的MAEKEIYAKUKIN_KEI(变更金额),如果不存在的话,则查询 PJ3500中的金额。
以下sql只能查询出有变更回数的数据,即只有PJ3510有数据时的数据。
SELECT A.nendo, A.ANKENNO, A.SHOZOKUKA, A.SEQNO, C.HENKOUCNT,C.MAEKEIYAKUKIN_KEI,D.string_value
FROM PJ3600 A, PJ3500 B, PJ3002 D,PJ3510 C,
(select NENDO,ANKENNO,SHOZOKUKA,KEIYAKUSHUBETSU,max(HENKOUCNT) AS HENKOUCNT from PJ3510
group by nendo,ANKENNO,SHOZOKUKA,KEIYAKUSHUBETSU) E
WHERE TO_CHAR(A.KENSAUKETUKEYMD, 'YYYYMMDD') >= '20080101'
AND TO_CHAR(A.KENSAUKETUKEYMD, 'YYYYMMDD') < '20090309'
AND A.KEIYAKUSHUBETSU = '4'
AND A.NENDO = B.NENDO
AND A.ANKENNO = B.ANKENNO
AND A.SHOZOKUKA = B.SHOZOKUKA
AND A.KEIYAKUSHUBETSU = B.KEIYAKUSHUBETSU
AND '1' = B.KENSAKBN
AND
C.NENDO(+) = B.NENDO
AND C.ANKENNO(+) = B.ANKENNO
AND C.SHOZOKUKA(+) = B.SHOZOKUKA
AND C.KEIYAKUSHUBETSU(+) = B.KEIYAKUSHUBETSU
-- AND C.HENKOUCNT =
AND
B.NENDO = D.NENDO(+)
AND B.ANKENNO = D.ANKENNO(+)
AND B.SHOZOKUKA = D.SHOZOKUKA(+)
AND B.KEIYAKUSHUBETSU = D.KEIYAKUSHUBETSU(+)
AND '1' = D.SEQNO(+)
AND 'PJ3G5020' = D.PAGEID(+)
AND '10' = D.YOBIID(+)
AND ((C.nendo = E.nendo AND C.ANKENNO = E.ANKENNO
AND C.SHOZOKUKA = E.SHOZOKUKA AND C.KEIYAKUSHUBETSU = E.KEIYAKUSHUBETSU AND C.HENKOUCNT = E.HENKOUCNT) or
not exists (select F.nendo from PJ3510 F where F.nendo = B.nendo AND F.SHOZOKUKA = B.SHOZOKUKA AND F.KEIYAKUSHUBETSU = B.KEIYAKUSHUBETSU
AND F.ANKENNO = B.ANKENNO)) 如果PJ3510没有数据时,也想查出来,我做了如下修改(添加如上蓝色部分),结果查出来很多。
求该sql如何写,或者是指出我写的错误部分。
<<検査検収結果情報>>
PJ3600.契約種別 = ‘4’ ANDPJ3600.年度=PJ3500.年度 AND
PJ3600.案件番号 = PJ3500.案件番号 AND
PJ3600.所属課 = PJ3500.所属課 AND
PJ3600.契約種別 = PJ3500.契約種別 AND<<契約結果情報>>
変更契約時: ※変更契約情報存在的时候
PJ3500.年度=PJ3510.年度 AND
PJ3500.案件番号 = PJ3510.案件番号 AND
PJ3500.所属課 = PJ3510.所属課 AND
PJ3500.契約種別 = PJ3510.契約種別 AND
変更契約情報.変更回数=変更回数(MAX値)要检索的是如果PJ3510(変更契約情報)存在数据的话,则查询最大变更回数 的MAEKEIYAKUKIN_KEI(变更金额),如果不存在的话,则查询 PJ3500中的金额。
以下sql只能查询出有变更回数的数据,即只有PJ3510有数据时的数据。
SELECT A.nendo, A.ANKENNO, A.SHOZOKUKA, A.SEQNO, C.HENKOUCNT,C.MAEKEIYAKUKIN_KEI,D.string_value
FROM PJ3600 A, PJ3500 B, PJ3002 D,PJ3510 C,
(select NENDO,ANKENNO,SHOZOKUKA,KEIYAKUSHUBETSU,max(HENKOUCNT) AS HENKOUCNT from PJ3510
group by nendo,ANKENNO,SHOZOKUKA,KEIYAKUSHUBETSU) E
WHERE TO_CHAR(A.KENSAUKETUKEYMD, 'YYYYMMDD') >= '20080101'
AND TO_CHAR(A.KENSAUKETUKEYMD, 'YYYYMMDD') < '20090309'
AND A.KEIYAKUSHUBETSU = '4'
AND A.NENDO = B.NENDO
AND A.ANKENNO = B.ANKENNO
AND A.SHOZOKUKA = B.SHOZOKUKA
AND A.KEIYAKUSHUBETSU = B.KEIYAKUSHUBETSU
AND '1' = B.KENSAKBN
AND
C.NENDO(+) = B.NENDO
AND C.ANKENNO(+) = B.ANKENNO
AND C.SHOZOKUKA(+) = B.SHOZOKUKA
AND C.KEIYAKUSHUBETSU(+) = B.KEIYAKUSHUBETSU
-- AND C.HENKOUCNT =
AND
B.NENDO = D.NENDO(+)
AND B.ANKENNO = D.ANKENNO(+)
AND B.SHOZOKUKA = D.SHOZOKUKA(+)
AND B.KEIYAKUSHUBETSU = D.KEIYAKUSHUBETSU(+)
AND '1' = D.SEQNO(+)
AND 'PJ3G5020' = D.PAGEID(+)
AND '10' = D.YOBIID(+)
AND ((C.nendo = E.nendo AND C.ANKENNO = E.ANKENNO
AND C.SHOZOKUKA = E.SHOZOKUKA AND C.KEIYAKUSHUBETSU = E.KEIYAKUSHUBETSU AND C.HENKOUCNT = E.HENKOUCNT) or
not exists (select F.nendo from PJ3510 F where F.nendo = B.nendo AND F.SHOZOKUKA = B.SHOZOKUKA AND F.KEIYAKUSHUBETSU = B.KEIYAKUSHUBETSU
AND F.ANKENNO = B.ANKENNO)) 如果PJ3510没有数据时,也想查出来,我做了如下修改(添加如上蓝色部分),结果查出来很多。
求该sql如何写,或者是指出我写的错误部分。
AND C.SHOZOKUKA = E.SHOZOKUKA AND C.KEIYAKUSHUBETSU = E.KEIYAKUSHUBETSU AND C.HENKOUCNT = E.HENKOUCNT)这个条件就查询不出数据了,用外连接吧