style24.doc/산출물/4.구축/데이터처리/06.브랜드그룹생성.sql

TRUNCATE TABLE TB_BRAND_GROUP;

INSERT INTO TB_BRAND_GROUP
SELECT
    BRAND_NO
    , BRAND_KNM
    , BRAND_ENM
    , 'EN' AS DISP_NM_LANG
    , NULL
    , NULL
    ,'222222' AS RGB_CD
    , DISP_ORD
    , 'Y'
    , NULL
    , 'Y'
    , 6
    , NOW()
    , 6
    , NOW()
FROM (
     SELECT
        BRAND_NO
        , BRAND_KNM
        , BRAND_ENM
        , DISP_ORD
        , RANK() over (PARTITION BY BRAND_ENM ORDER BY SELF_YN DESC, BRAND_NO ASC) AS RNK
    FROM tb_brand
    WHERE BRAND_ENM IS NOT NULL
) A
WHERE A.RNK = 1
;

UPDATE tb_brand A
SET BRAND_GROUP_NO = (SELECT BRAND_GROUP_NO FROM tb_brand_group X WHERE X.BRAND_GROUP_ENM = A.BRAND_ENM)
WHERE A.BRAND_ENM IS NOT NULL
;

SELECT * FROM tb_brand WHERE BRAND_GROUP_NO IS NULL;

INSERT INTO TB_BRAND_GROUP
SELECT
    BRAND_NO
    , BRAND_KNM
    , BRAND_ENM
    , 'KO' AS DISP_NM_LANG
    , NULL
    , NULL
    ,'222222' AS RGB_CD
    , DISP_ORD
    , 'Y'
    , NULL
    , 'Y'
    , 6
    , NOW()
    , 6
    , NOW()
FROM (
     SELECT
        BRAND_NO
        , BRAND_KNM
        , BRAND_ENM
        , DISP_ORD
        , RANK() over (PARTITION BY BRAND_KNM ORDER BY SELF_YN DESC, BRAND_NO ASC) AS RNK
    FROM tb_brand A
    WHERE BRAND_ENM IS NULL
        AND NOT EXISTS(
            SELECT 1
            FROM tb_brand_group X
            WHERE X.BRAND_GROUP_NO = A.BRAND_NO
        )
) A
WHERE A.RNK = 1
;

UPDATE tb_brand A
SET BRAND_GROUP_NO = (SELECT BRAND_GROUP_NO FROM tb_brand_group X WHERE X.BRAND_GROUP_KNM = A.BRAND_KNM)
WHERE A.BRAND_ENM IS NULL
;