部分 检索条件
<<検査検収結果情報>>
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如何写,或者是指出我写的错误部分。
解决方案 »
- oracle 10g中没有wmsys用户,如何重建
- 如何在Oracle建立用户仅能select所有表和视图
- 存储过程监控中间结果
- toad连oracle死机怎么回事?
- 求个Oralce 10G的下载地址能用的。。。
- 【愁死了】ORA-03115错误,大伙们来帮帮忙啊,弄2天了,快被搞疯掉了~!!!
- 关于ORACLE的帮助文档
- 我们公司的数据库在表中都是用两列分别记录日期和时间的,怎么用sql得出时间差?
- 请看我这样用group by对吗?
- 怎样在db2中修改存储过程的名字!(急!!!!!!!!!!!!!!!!!!)
- 紧急求救,关于oracle数据库启动问题。请各位大侠帮帮忙!
- 如何执行.hql文件
AND C.SHOZOKUKA = E.SHOZOKUKA AND C.KEIYAKUSHUBETSU = E.KEIYAKUSHUBETSU AND C.HENKOUCNT = E.HENKOUCNT)这个条件就查询不出数据了,用外连接吧