学步园

表一：TRUSTEEBANK  BANK信息表

表二：GLOBALCONST  SEC常量定义表，包含多种类型的常量定义，本例用到certificateType类型

表三：LOOKUPCODEMAPPING SEC和各个BANK常量定义的映射表

目的：找出各银行在LOOKUPCODEMAPPING表中与GLOBALCONST表没有映射关系的项。

SELECT A.TRUSTEEBANKID, A.NAME, B.INTERIORID SECCODE, B.CONSTDISPLAYNAME SECDESC, '' BANKCODE, '' BANKDESC
    FROM TRUSTEEBANK A, (SELECT * FROM GLOBALCONST  WHERE GLOBALCONST = 'certificateType') B
    WHERE B.INTERIORID NOT IN (SELECT C.SECCODE FROM LOOKUPCODEMAPPING C WHERE C.TRUSTEEBANKID = A.TRUSTEEBANKID AND C.LOOKUPCODE = 'certificateType');