语句:
SELECT T3.ID,T3.REGCODE,T3.SUBCODE,T4.REGNAME,T4.SUBNAME,T4.LEVELCODE,T4.LEVELDESC,T4.CLASSCODE,T4.CLASSDESC,T4.AGECODE,T4.AGEDESC
FROM (SELECT T1.SEQID AS ID,T2.NUM AS REGCODE,T1.NUM AS SUBCODE FROM CDI_CHILD T1,CDI_MAIN T2 WHERE T1.MAINID=T2.SEQID ORDER BY T1.SEQID) T3
LEFT JOIN ARTICLE_CATALOG T4 ON T3.REGCODE=T4.REGCODE AND (T3.SUBCODE=T4.SUBCODE OR (T4.SUBCODE IS NULL AND T3.SUBCODE='0') OR (T4.SUBTYPE='ZH' AND T3.SUBCODE='0'));
表结构
CDI_CHILD
SEQID Number(12)
MAINID Number(12)
NUM VARCHAR(16)CDI_MAIN
SEQID Number(12)
NUM VARCHAR(32)ARTICLE_CATALOG
SEQID NUMBER(12) not null,
REGCODE VARCHAR2(32 CHAR),
REGNAME VARCHAR2(300 CHAR),
SUBTYPE CHAR(2 CHAR),
SUBCODE VARCHAR2(16 CHAR),
SUBNAME VARCHAR2(240 CHAR),
ORIGINALNAME VARCHAR2(240 CHAR),
LEVELCODE CHAR(2 CHAR),
LEVELDESC VARCHAR2(60 CHAR),
CLASSCODE VARCHAR2(2 CHAR),
CLASSDESC VARCHAR2(60 CHAR),
AGECODE VARCHAR2(12 CHAR),
AGEDESC VARCHAR2(60 CHAR),
NATIONCODE NUMBER(4),
NATIONDESC VARCHAR2(60 CHAR),
COLLECTNUM VARCHAR2(32 CHAR),
WEIGHT VARCHAR2(60 CHAR),
WEIGHTUNIT VARCHAR2(10 CHAR),
CUBATURE VARCHAR2(60 CHAR),
CUBATUREUNIT VARCHAR2(10 CHAR),
COUNTS NUMBER(8),
COUNTRY VARCHAR2(60 CHAR),
AGEADDITIONAL VARCHAR2(120 CHAR),
PRODUCTAREA VARCHAR2(300 CHAR),
YEARDATA VARCHAR2(120 CHAR),
INNERSIZE VARCHAR2(300 CHAR),
INNERSIZEUNIT VARCHAR2(10 CHAR),
OUTERSIZE VARCHAR2(300 CHAR),
OUTERSIZEUNIT VARCHAR2(10 CHAR),
INSCRIPTION VARCHAR2(240 CHAR),
SHAPE VARCHAR2(120 CHAR),
COLOR VARCHAR2(120 CHAR),
THUMBNAIL VARCHAR2(64 CHAR),
GENRE VARCHAR2(120 CHAR),
DEPTID CHAR(2 CHAR),
KEEPSTATUS VARCHAR2(3000 CHAR),
CREDITNUM VARCHAR2(32 CHAR),
SAVECREDIT VARCHAR2(32 CHAR),
SAVEDATE DATE,
SAVEPLACE VARCHAR2(120 CHAR),
SAVEUSER NUMBER(12),
SAVETIME TIMESTAMP(6),
INPUTUSER NUMBER(12),
INPUTUSERNAME VARCHAR2(30 CHAR),
INPUTTIME TIMESTAMP(6),
VERIFYUSER NUMBER(12),
VERIFYTIME TIMESTAMP(6),
PUBUSER NUMBER(12),
PUBTIME TIMESTAMP(6),
DELUSER NUMBER(12),
DELTIME TIMESTAMP(6),
STATUS CHAR(2 CHAR),
OLDID NUMBER(12),
REGCODE2 VARCHAR2(32 CHAR),
CPCATALOGYN VARCHAR2(10 CHAR) default 0
SELECT T3.ID,T3.REGCODE,T3.SUBCODE,T4.REGNAME,T4.SUBNAME,T4.LEVELCODE,T4.LEVELDESC,T4.CLASSCODE,T4.CLASSDESC,T4.AGECODE,T4.AGEDESC
FROM (SELECT T1.SEQID AS ID,T2.NUM AS REGCODE,T1.NUM AS SUBCODE FROM CDI_CHILD T1,CDI_MAIN T2 WHERE T1.MAINID=T2.SEQID ORDER BY T1.SEQID) T3
LEFT JOIN ARTICLE_CATALOG T4 ON T3.REGCODE=T4.REGCODE AND (T3.SUBCODE=T4.SUBCODE OR (T4.SUBCODE IS NULL AND T3.SUBCODE='0') OR (T4.SUBTYPE='ZH' AND T3.SUBCODE='0'));
表结构
CDI_CHILD
SEQID Number(12)
MAINID Number(12)
NUM VARCHAR(16)CDI_MAIN
SEQID Number(12)
NUM VARCHAR(32)ARTICLE_CATALOG
SEQID NUMBER(12) not null,
REGCODE VARCHAR2(32 CHAR),
REGNAME VARCHAR2(300 CHAR),
SUBTYPE CHAR(2 CHAR),
SUBCODE VARCHAR2(16 CHAR),
SUBNAME VARCHAR2(240 CHAR),
ORIGINALNAME VARCHAR2(240 CHAR),
LEVELCODE CHAR(2 CHAR),
LEVELDESC VARCHAR2(60 CHAR),
CLASSCODE VARCHAR2(2 CHAR),
CLASSDESC VARCHAR2(60 CHAR),
AGECODE VARCHAR2(12 CHAR),
AGEDESC VARCHAR2(60 CHAR),
NATIONCODE NUMBER(4),
NATIONDESC VARCHAR2(60 CHAR),
COLLECTNUM VARCHAR2(32 CHAR),
WEIGHT VARCHAR2(60 CHAR),
WEIGHTUNIT VARCHAR2(10 CHAR),
CUBATURE VARCHAR2(60 CHAR),
CUBATUREUNIT VARCHAR2(10 CHAR),
COUNTS NUMBER(8),
COUNTRY VARCHAR2(60 CHAR),
AGEADDITIONAL VARCHAR2(120 CHAR),
PRODUCTAREA VARCHAR2(300 CHAR),
YEARDATA VARCHAR2(120 CHAR),
INNERSIZE VARCHAR2(300 CHAR),
INNERSIZEUNIT VARCHAR2(10 CHAR),
OUTERSIZE VARCHAR2(300 CHAR),
OUTERSIZEUNIT VARCHAR2(10 CHAR),
INSCRIPTION VARCHAR2(240 CHAR),
SHAPE VARCHAR2(120 CHAR),
COLOR VARCHAR2(120 CHAR),
THUMBNAIL VARCHAR2(64 CHAR),
GENRE VARCHAR2(120 CHAR),
DEPTID CHAR(2 CHAR),
KEEPSTATUS VARCHAR2(3000 CHAR),
CREDITNUM VARCHAR2(32 CHAR),
SAVECREDIT VARCHAR2(32 CHAR),
SAVEDATE DATE,
SAVEPLACE VARCHAR2(120 CHAR),
SAVEUSER NUMBER(12),
SAVETIME TIMESTAMP(6),
INPUTUSER NUMBER(12),
INPUTUSERNAME VARCHAR2(30 CHAR),
INPUTTIME TIMESTAMP(6),
VERIFYUSER NUMBER(12),
VERIFYTIME TIMESTAMP(6),
PUBUSER NUMBER(12),
PUBTIME TIMESTAMP(6),
DELUSER NUMBER(12),
DELTIME TIMESTAMP(6),
STATUS CHAR(2 CHAR),
OLDID NUMBER(12),
REGCODE2 VARCHAR2(32 CHAR),
CPCATALOGYN VARCHAR2(10 CHAR) default 0
解决方案 »
- 怎么查看数据文件的具体内容
- ORA-12638:身份证明检索失败
- PL/SQL中一打逗号就死机了?
- 求一SQL语句
- 为什么网上说plsql存储过程一定比c语言、java语言写的存储过程的执行速度快而且执行效率要高呢?
- 请问怎样用SQL命令在SQL Plus建立一个新的数据库.
- v$session里的连接的会话怎么才能被真正的KILL掉??
- 一个与数据库连接问题,请大侠帮忙。在线等待。
- ora-12801
- 【紧急求助!】oracle触发器中如何获取某一个表的insert和delete语句操作失败
- 请求大神帮助,关于oracle生成windows应用程序日志
- mac 下连接 occi 报错 ORA-24960
T2.NUM AS REGCODE,
T1.NUM AS SUBCODE,
T4.REGNAME,
T4.SUBNAME,
T4.LEVELCODE,
T4.LEVELDESC,
T4.CLASSCODE,
T4.CLASSDESC,
T4.AGECODE,
T4.AGEDESC
FROM CDI_CHILD T1
INNER JOIN CDI_MAIN T2
ON T1.MAINID = T2.SEQID
LEFT JOIN ARTICLE_CATALOG T4
ON T2.NUM = T4.REGCODE
AND (T1.NUM = T4.SUBCODE OR (T4.SUBCODE IS NULL AND T1.NUM = '0') OR
(T4.SUBTYPE = 'ZH' AND T1.NUM = '0'))
ORDER BY T1.SEQID;
应该直接把 T1、T2 提到外面来。
SELECT T1.SEQID AS ID,
T2.NUM AS REGCODE,
T1.NUM AS SUBCODE,
T4.REGNAME,
T4.SUBNAME,
T4.LEVELCODE,
T4.LEVELDESC,
T4.CLASSCODE,
T4.CLASSDESC,
T4.AGECODE,
T4.AGEDESC
FROM CDI_CHILD T1
JOIN CDI_MAIN T2
ON T1.MAINID = T2.SEQID
LEFT JOIN ARTICLE_CATALOG T4
ON T2.NUM = T4.REGCODE
AND ( (T1.NUM = T4.SUBCODE)
OR (T4.SUBCODE IS NULL AND T1.NUM='0')
OR (T4.SUBTYPE='ZH' AND T1.NUM='0')
)
WHERE ...
还有建议按下面的方式建索引
CDI_CHILD(MAINID,NUM,SEQID)
CDI_MAIN(SEQID,NUM)
ARTICLE_CATALOG(REGCODE,SUBCODE,SUBTYPE)