insert---select

最新推荐文章于 2021-12-29 14:43:41 发布

qq_25986811

最新推荐文章于 2021-12-29 14:43:41 发布

阅读量221

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_25986811/article/details/79739060

版权

sql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

INSERT INTO T_CORP_LS
  (UUID,
   CCDX_NAME,
   UUIT_NO,
   BELONG_ORG,
   OPER_MAN_NAME,
   START_DATE,
   CCDX_TYPE,
   ORG_ID,
   USER_ID,
   PK_ORG,
   PK_ID,
   CORP_ORG,
   CORP_ID,
   ZJ_ECON_KIND,
   BELONG_TRADE,
   BELONG_DIST_ORG,
   FARE_SCOPE,
   BELONG_ORG_ID,
   CORP_TYPE,
   IF_CHOOSE)
  SELECT UUID,
         CCDX_NAME,
         UUIT_NO,
         BELONG_ORG,
         OPER_MAN_NAME,
         TO_DATE(START_DATE, 'YYYY-MM-DD'),
         1,
         666, --'用户orgid',
         666, --'用户userid',
         PK_ORG,
         PK_ID,
         CORP_ORG,
         CORP_ID,
         ZJ_ECON_KIND,
         BELONG_TRADE,
         CASE BELONG_DIST_ORG
           WHEN 0 THEN -- 所属管区ID是否为空
            CASE
              WHEN EXISTS (SELECT PARENT_ID -- 是否为工商所
                      FROM R$ZZLD_ORG
                     WHERE LEVEL_NAME = '6'
                       AND STATE = 'A '
                       AND ORG_ID = 1651) THEN
               (SELECT PARENT_ID
                  FROM R$ZZLD_ORG
                 WHERE LEVEL_NAME = '6'
                   AND STATE = 'A '
                   AND ORG_ID = 1651)
              ELSE
               BELONG_ORG_ID
            END
           ELSE
            BELONG_ORG_ID
         END BELONG_DIST_ORG,
         FARE_SCOPE,
         BELONG_ORG_ID,
         1,
         1
    FROM (SELECT *
            FROM (SELECT TMP.*
                    FROM (SELECT SYS_GUID() UUID,
                                 A.ORG PK_ORG,
                                 A.ID PK_ID,
                                 A.CORP_NAME CCDX_NAME,
                                 A.REG_NO UUIT_NO,
                                 A.BELONG_ORG BELONG_ORG,
                                 A.OPER_MAN_NAME OPER_MAN_NAME,
                                 TO_CHAR(A.START_DATE, 'YYYY-MM-DD') START_DATE,
                                 A.CORP_ORG CORP_ORG,
                                 A.CORP_ID CORP_ID,
                                 A.ZJ_ECON_KIND ZJ_ECON_KIND,
                                 A.BELONG_TRADE BELONG_TRADE,
                                 A.FARE_SCOPE FARE_SCOPE,
                                 A.BELONG_ORG_ID BELONG_ORG_ID,
                                 NVL(A.BELONG_DIST_ORG, 0) BELONG_DIST_ORG
                            FROM T_GS_COLLECT_INFO_BK   A,
                                 T_SUPERVISE_CORP       B,
                                 T_DICT_TRADE           D,
                                 T_SUPERVISE_CHANGE_INF P
                           WHERE B.CORP_ORG = A.CORP_ORG
                             AND B.CORP_ID = A.CORP_ID
                             AND B.CLAIM_STATUS = '01'
                             AND A.BELONG_TRADE = D.ID(+)
                             AND B.SUPERVISE_ID = P.SUPERVISE_ID(+)
                             AND A.CORP_STATUS IN ('01', '02', '12')
                             AND B.CLAIM_ORG = '0') TMP
                   ORDER BY dbms_random.value) TMP2
           WHERE ROWNUM <= '12')