style24.doc/산출물/4.구축/데이터처리/다음EP생성_20210614.sql

################################################################################
#EP다음
################################################################################
DROP TABLE IF EXISTS TB_EP_DAUM RESTRICT;
CREATE TABLE TB_EP_DAUM (
  GB            VARCHAR(4)     NOT NULL COMMENT '구분(PREV:이전, CURR:현재)',
  MAPID         VARCHAR(20)    NOT NULL COMMENT '상품코드(상품)',
  LPRICE        INT UNSIGNED   COMMENT '정상가',
  PRICE         INT UNSIGNED   COMMENT 'PC가격',
  MPRIC         INT UNSIGNED   COMMENT '모바일가격',
  PNAME         VARCHAR(100)   COMMENT '상품명([상품구분명]+[브랜드명]+상품명+상품코드 형식)',
  PGURL         VARCHAR(200)   COMMENT 'PC상품상세URL',
  IGURL         VARCHAR(200)   COMMENT '이미지링크',
  UPIMG         VARCHAR(1)     DEFAULT 'N' COMMENT '전체EP생성 이후 이미지변경되었을때전송(Y/N)',
  CATE1         VARCHAR(50)    COMMENT '카테고리명1',
  CAID1         VARCHAR(10)    COMMENT '카테고리ID1',
  CATE2         VARCHAR(50)    COMMENT '카테고리명2',
  CAID2         VARCHAR(10)    COMMENT '카테고리ID2',
  CATE3         VARCHAR(50)    COMMENT '카테고리명3',
  CAID3         VARCHAR(10)    COMMENT '카테고리ID3',
  CATE4         VARCHAR(50)    COMMENT '카테고리명4',
  CAID4         VARCHAR(10)    COMMENT '카테고리ID4',
  MODEL         VARCHAR(20)    COMMENT '모델(품번)',
  BRAND         VARCHAR(50)    COMMENT '브랜드명',
  MAKER         VARCHAR(50)    COMMENT '제조사',
  COUPON        VARCHAR(100)   COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)',
  MCOUPON       VARCHAR(100)   COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)',
  PCARD         VARCHAR(100)   COMMENT '무이자행사(카드명^개월수-개월수|카드명^개월수-개월수... 형식)',
  POINT         VARCHAR(10)    COMMENT '포인트율',
  DELIV         INT UNSIGNED   COMMENT '배송료',
  DELIVTERM     INT UNSIGNED   COMMENT '배송기간',
  RATING        VARCHAR(20)    COMMENT '상품평점수(4.5/5 형식)',
  REVCT         INT UNSIGNED   COMMENT '상품평수',
  EVENT         VARCHAR(100)   COMMENT '상품타이틀명(세일링문구)',
  CARDDN        VARCHAR(100)   COMMENT '카드행사(카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가|카드명^카드적용가... 형식)',
  CARDP         INT UNSIGNED   COMMENT '카드적용가(최대1개)',
  ADULT         VARCHAR(1)     COMMENT '성인상품여부',
  PUBDATE       VARCHAR(8)     COMMENT '상품등록일',
  GOODS_STAT    VARCHAR(20)    COMMENT '상품상태(공통코드G008)',
  SELF_MALL_YN  CHAR(1)        COMMENT '자사몰노출여부',
  SELL_STDT     DATETIME       COMMENT '판매시작일시',
  SELL_EDDT     DATETIME       COMMENT '판매종료일시',
  UPD_GB        VARCHAR(1)     COMMENT '업데이트구분(I:신규상품, U:기존상품중업데이트된상품 또는 품절되었다가다시서비스되는상품, D:품절상품)',
  UTIME         VARCHAR(20)    COMMENT '업데이트시간(YYYYMMDDHHMMSS 형식)'
)
COMMENT 'EP다음. EP다음 생성에 의한 발생';

CREATE UNIQUE INDEX IX_EP_DAUM_01
  ON TB_EP_DAUM (
    GB, MAPID
  );

-- EP다음 정보 삭제
DELETE FROM TB_EP_DAUM WHERE GB = 'PREV';

-- EP다음 이전 데이터로 백업
INSERT INTO TB_EP_DAUM (
       GB
     , MAPID
     , LPRICE
     , PRICE
     , MPRIC
     , PNAME
     , PGURL
     , IGURL
     , CATE1
     , CAID1
     , CATE2
     , CAID2
     , CATE3
     , CAID3
     , CATE4
     , CAID4
     , MODEL
     , BRAND
     , MAKER
     , COUPON
     , MCOUPON
     , PCARD
     , POINT
     , DELIV
     , DELIVTERM
     , RATING
     , REVCT
     , EVENT
     , CARDDN
     , CARDP
     , ADULT
     , PUBDATE
     , GOODS_STAT
     , SELF_MALL_YN
     , SELL_STDT
     , SELL_EDDT
)
SELECT 'PREV' AS GB
     , MAPID
     , LPRICE
     , PRICE
     , MPRIC
     , PNAME
     , PGURL
     , IGURL
     , CATE1
     , CAID1
     , CATE2
     , CAID2
     , CATE3
     , CAID3
     , CATE4
     , CAID4
     , MODEL
     , BRAND
     , MAKER
     , COUPON
     , MCOUPON
     , PCARD
     , POINT
     , DELIV
     , DELIVTERM
     , RATING
     , REVCT
     , EVENT
     , CARDDN
     , CARDP
     , ADULT
     , PUBDATE
     , GOODS_STAT
     , SELF_MALL_YN
     , SELL_STDT
     , SELL_EDDT
FROM   TB_EP_DAUM
WHERE  GB = 'LAST'
;

-- EP다음 현재 데이터 생성 (2분 6초)
INSERT INTO TB_EP_DAUM (
       GB
     , MAPID
     , LPRICE
     , PRICE
     , MPRIC
     , PNAME
     , PGURL
     , IGURL
     , CATE1
     , CAID1
     , CATE2
     , CAID2
     , CATE3
     , CAID3
     , CATE4
     , CAID4
     , MODEL
     , BRAND
     , MAKER
     , COUPON
     , MCOUPON
     , PCARD
     , POINT
     , DELIV
     , DELIVTERM
     , RATING
     , REVCT
     , EVENT
     , CARDDN
     , CARDP
     , ADULT
     , PUBDATE
     , GOODS_STAT
     , SELF_MALL_YN
     , SELL_STDT
     , SELL_EDDT
)
SELECT 'CURR'                                                                                              AS GB         /*구분(PREV:이전, CURR:현재)*/
     , G.GOODS_CD                                                                                          AS MAPID      /*상품ID*/
     , G.LIST_PRICE                                                                                        AS LPRICE     /*정상가*/
     , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY                                              AS PRICE      /*즉시할인이적용된가격*/
     , IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY                                              AS MPRIC      /*즉시할인이적용된가격*/
     , SUBSTRING(CONCAT(CASE WHEN CC1.CD IN ('G073_12','G073_13') THEN CONCAT('[',CC1.CD_NM,']')
                             WHEN G.FOREIGN_BUY_YN = 'Y' THEN '[해외]'
                             ELSE ''
                        END,
                       '[',CASE WHEN B.DISP_NM_LANG = 'EN' THEN B.BRAND_ENM ELSE B.BRAND_KNM END,'] ',
                       G.GOODS_NM,
                       IFNULL(G.GOODS_NUM,'')),1,100)                                                      AS PNAME      /*상품명*/
     , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',G.GOODS_CD)                             AS PGURL      /*PC상세URL*/
     , GI.IMAGE_LINK                                                                                       AS IGURL      /*이미지URL*/
     , SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1)                                                                AS CATE1      /*카테고리명1*/
     , SUBSTRING(G.ITEMKIND_CD,1,2)                                                                        AS CAID1      /*카테고리ID1*/
     , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1) THEN
                NULL
            ELSE
                SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1)
       END                                                                                                 AS CATE2      /*카테고리명2*/
     , SUBSTRING(G.ITEMKIND_CD,3,2)                                                                        AS CAID2      /*카테고리ID2*/
     , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1) THEN
                NULL
            ELSE
                SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1)
       END                                                                                                 AS CATE3      /*카테고리명3*/
     , SUBSTRING(G.ITEMKIND_CD,5,2)                                                                        AS CAID3      /*카테고리ID3*/
     , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',4),'>',-1) THEN
                NULL
            ELSE
                SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',4),'>',-1)
       END                                                                                                 AS CATE4      /*카테고리명4*/
     , SUBSTRING(G.ITEMKIND_CD,7,2)                                                                        AS CAID4      /*카테고리ID4*/
     , G.GOODS_NUM                                                                                         AS MODEL      /*모델(품번)*/
     , IF(B.DISP_NM_LANG = 'EN',B.BRAND_ENM,B.BRAND_KNM)                                                   AS BRAND      /*브랜드명*/
     , SUBSTRING(GNI.NI_CONTENT,1,50)                                                                      AS MAKER      /*제조사*/
     , TEC.COUPON                                                                                                        /*쿠폰*/
     , TEC.COUPON                                                                                          AS MCOUPON    /*모바일쿠폰*/
     , IFE.INTEREST_FREE_EVENT                                                                             AS PCARD      /*무이자행사*/
     , CONCAT('',G.PNT_PRATE)                                                                              AS POINT      /*포인트율*/
     , IFNULL(CASE WHEN DFP.DELV_FEE_CRITE = 'G078_20' /*무료*/ THEN
                       0
                   ELSE /*조건부무료 또는 유료*/
                       CASE WHEN DFP.MIN_ORD_AMT <= IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY THEN
                                0
                            ELSE
                                DFP.DELV_FEE
                       END
              END,0)                                                                                       AS DELIV      /*배송료*/
     , CASE WHEN G.SELF_GOODS_YN = 'Y' THEN 1 ELSE 3 END                                                   AS DELIVTERM  /*배송기간(자사상품:1일,입점상품:3일)*/
     , CASE WHEN GS.SCORE IS NULL OR GS.SCORE = 0.0 THEN ''
            ELSE CONCAT(IFNULL(GS.SCORE,0),'/',5)
       END                                                                                                 AS RATING     /*상품평평점*/
     , IFNULL(GS.REVIEW_REG_CNT,0)                                                                         AS REVCT      /*상품평수*/
     , G.GOODS_TNM                                                                                         AS EVENT      /*상품셀링문구*/
     , SUBSTRING_INDEX(SUBSTRING_INDEX(CE.CARD_EVENT,'|',1),'^',1)                                         AS CARDDN     /*카드행사*/
     , SUBSTRING(SUBSTRING_INDEX(CE.CARD_EVENT,'|',1),INSTR(SUBSTRING_INDEX(CE.CARD_EVENT,'|',1),'^') + 1) AS CARDP      /*카드할인가*/
     , G.ADULT_YN                                                                                          AS ADULT      /*성인여부*/
     , DATE_FORMAT(G.REG_DT,'%Y%m%d')                                                                      AS PUBDATE    /*등록일*/
     , G.GOODS_STAT
     , G.SELF_MALL_YN
     , G.SELL_STDT
     , G.SELL_EDDT
FROM   TB_GOODS G
INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
INNER JOIN TB_ITEMKIND I ON G.ITEMKIND_CD = I.ITEMKIND_CD
INNER JOIN TB_DELV_FEE_POLICY DFP ON G.DELV_FEE_CD = DFP.DELV_FEE_CD
LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
LEFT OUTER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
LEFT OUTER JOIN TB_EP_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
LEFT OUTER JOIN TB_EP_GOODS_ADD_IMG GAI ON G.GOODS_CD = GAI.GOODS_CD
LEFT OUTER JOIN (
                 SELECT ITEMKIND_CD
                      , MIN(EP_CATE_CD) AS EP_CATE_CD
                 FROM   TB_EP_CATE
                 WHERE  EP_GB = '10' /*네이버*/
                 AND    USE_YN = 'Y'
                 GROUP  BY ITEMKIND_CD
                ) EC ON G.ITEMKIND_CD = EC.ITEMKIND_CD
LEFT OUTER JOIN TB_GOODS_RES_SELL GRS ON G.GOODS_CD = GRS.GOODS_CD
                                     AND GRS.DELV_RES_DT >= NOW()
                                     AND GRS.USE_YN = 'Y'
LEFT OUTER JOIN TB_EP_CARD_EVENT CE ON G.GOODS_CD = CE.GOODS_CD
LEFT OUTER JOIN TB_EP_COUPON TEC ON G.GOODS_CD = TEC.GOODS_CD
LEFT OUTER JOIN TB_EP_INTEREST_FREE_EVENT IFE ON G.GOODS_CD = IFE.GOODS_CD
LEFT OUTER JOIN TB_GOODS_NOTI_INFO GNI ON G.GOODS_CD = GNI.GOODS_CD
                                      AND GNI.NI_ITEM_CD = 'G005_005' /*제조사*/
LEFT OUTER JOIN TB_COMMON_CODE CC1 ON G.GOODS_GB = CC1.CD
                                 AND CC1.CD_GB = 'G073'
LEFT OUTER JOIN TB_COMMON_CODE CC2 ON G.ORIGIN_CD = CC2.CD
                                  AND CC2.CD_GB = 'G076'
WHERE  1 = 1
AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
AND    G.SELL_STDT <= NOW() /*유효한 판매기간*/
AND    G.SELL_EDDT >= NOW() /*유효한 판매기간*/
AND    G.ADULT_YN = 'N' /*성인상품아닌넘만*/
-- AND    G.UPD_DT >= DATE_ADD(NOW(), INTERVAL -7 HOUR) -- 요약EP
;

-- 최종 EP다음 데이터 생성 (45초)
INSERT INTO TB_EP_DAUM (
       GB
     , MAPID
     , LPRICE
     , PRICE
     , MPRIC
     , PNAME
     , PGURL
     , IGURL
     , UPIMG
     , CATE1
     , CAID1
     , CATE2
     , CAID2
     , CATE3
     , CAID3
     , CATE4
     , CAID4
     , MODEL
     , BRAND
     , MAKER
     , COUPON
     , MCOUPON
     , PCARD
     , POINT
     , DELIV
     , DELIVTERM
     , RATING
     , REVCT
     , EVENT
     , CARDDN
     , CARDP
     , ADULT
     , PUBDATE
     , GOODS_STAT
     , SELF_MALL_YN
     , SELL_STDT
     , SELL_EDDT
     , UPD_GB
     , UTIME
)
SELECT 'LAST'                                 AS GB
     , MAPID
     , LPRICE
     , PRICE
     , MPRIC
     , PNAME
     , PGURL
     , IGURL
     , UPIMG
     , CATE1
     , CAID1
     , CATE2
     , CAID2
     , CATE3
     , CAID3
     , CATE4
     , CAID4
     , MODEL
     , BRAND
     , MAKER
     , COUPON
     , MCOUPON
     , PCARD
     , POINT
     , DELIV
     , DELIVTERM
     , RATING
     , REVCT
     , EVENT
     , CARDDN
     , CARDP
     , ADULT
     , PUBDATE
     , GOODS_STAT
     , SELF_MALL_YN
     , SELL_STDT
     , SELL_EDDT
     , UPD_GB
     , DATE_FORMAT(NOW(),'%Y%m%d%H%i%S') AS UTIME
FROM   (
        /*신규 목록*/
        SELECT MAPID
             , LPRICE
             , PRICE
             , MPRIC
             , PNAME
             , PGURL
             , IGURL
             , 'N'           AS UPIMG
             , CATE1
             , CAID1
             , CATE2
             , CAID2
             , CATE3
             , CAID3
             , CATE4
             , CAID4
             , MODEL
             , BRAND
             , MAKER
             , COUPON
             , MCOUPON
             , PCARD
             , POINT
             , DELIV
             , DELIVTERM
             , RATING
             , REVCT
             , EVENT
             , CARDDN
             , CARDP
             , ADULT
             , PUBDATE
             , GOODS_STAT
             , SELF_MALL_YN
             , SELL_STDT
             , SELL_EDDT
             , 'I'           AS UPD_GB /*변경구분*/
        FROM   TB_EP_DAUM X
        WHERE  GB = 'CURR' /*현재데이터*/
        -- 요약EP
--        AND    NOT EXISTS (SELECT 1
--                           FROM   TB_EP_DAUM
--                           WHERE  GB = 'PREV' /*이전백업데이터*/
--                           AND    ID = X.ID
--        UNION ALL
--        /*변경 목록*/
--        SELECT X.MAPID
--             , X.LPRICE
--             , X.PRICE
--             , X.MPRIC
--             , X.PNAME
--             , X.PGURL
--             , X.IGURL
--             , CASE WHEN X.IGURL != Y.IGURL THEN 'Y' ELSE 'N' END AS UPIMG
--             , X.CATE1
--             , X.CAID1
--             , X.CATE2
--             , X.CAID2
--             , X.CATE3
--             , X.CAID3
--             , X.CATE4
--             , X.CAID4
--             , X.MODEL
--             , X.BRAND
--             , X.MAKER
--             , X.COUPON
--             , X.MCOUPON
--             , X.PCARD
--             , X.POINT
--             , X.DELIV
--             , X.DELIVTERM
--             , X.RATING
--             , X.REVCT
--             , X.EVENT
--             , X.CARDDN
--             , X.CARDP
--             , X.ADULT
--             , X.PUBDATE
--             , X.GOODS_STAT
--             , X.SELF_MALL_YN
--             , X.SELL_STDT
--             , X.SELL_EDDT
--             , 'U'                         AS UPD_GB /*변경구분*/
--        FROM   TB_EP_DAUM X
--             , TB_EP_DAUM Y
--        WHERE  X.ID = Y.ID
--        AND    X.GB = 'CURR' /*현재데이터*/
--        AND    Y.GB = 'PREV' /*이전백업데이터*/
--        AND    (
--                X.MAPID != Y.MAPID OR
--                X.LPRICE != Y.LPRICE OR
--                X.PRICE != Y.PRICE OR
--                X.MPRIC != Y.MPRIC OR
--                X.PNAME != Y.PNAME OR
--                X.PGURL != Y.PGURL OR
--                X.IGURL != Y.IGURL OR
--                X.CATE1 != Y.CATE1 OR
--                X.CAID1 != Y.CAID1 OR
--                X.CATE2 != Y.CATE2 OR
--                X.CAID2 != Y.CAID2 OR
--                X.CATE3 != Y.CATE3 OR
--                X.CAID3 != Y.CAID3 OR
--                X.CATE4 != Y.CATE4 OR
--                X.CAID4 != Y.CAID4 OR
--                X.MODEL != Y.MODEL OR
--                X.BRAND != Y.BRAND OR
--                X.MAKER != Y.MAKER OR
--                X.COUPON != Y.COUPON OR
--                X.MCOUPON != Y.MCOUPON OR
--                X.PCARD != Y.PCARD OR
--                X.POINT != Y.POINT OR
--                X.DELIV != Y.DELIV OR
--                X.DELIVTERM != Y.DELIVTERM OR
--                X.RATING != Y.RATING OR
--                X.REVCT != Y.REVCT OR
--                X.EVENT != Y.EVENT OR
--                X.CARDDN != Y.CARDDN OR
--                X.CARDP != Y.CARDP OR
--                X.ADULT != Y.ADULT OR
--                X.PUBDATE != Y.PUBDATE
--               )
--        UNION ALL
--        /*삭제된 목록*/
--        SELECT X.MAPID
--             , X.LPRICE
--             , X.PRICE
--             , X.MPRIC
--             , X.PNAME
--             , X.PGURL
--             , X.IGURL
--             , CASE WHEN X.IGURL != Y.IGURL THEN 'Y' ELSE 'N' END AS UPIMG
--             , X.CATE1
--             , X.CAID1
--             , X.CATE2
--             , X.CAID2
--             , X.CATE3
--             , X.CAID3
--             , X.CATE4
--             , X.CAID4
--             , X.MODEL
--             , X.BRAND
--             , X.MAKER
--             , X.COUPON
--             , X.MCOUPON
--             , X.PCARD
--             , X.POINT
--             , X.DELIV
--             , X.DELIVTERM
--             , X.RATING
--             , X.REVCT
--             , X.EVENT
--             , X.CARDDN
--             , X.CARDP
--             , X.ADULT
--             , X.PUBDATE
--             , X.GOODS_STAT
--             , X.SELF_MALL_YN
--             , X.SELL_STDT
--             , X.SELL_EDDT
--             , 'D'                         AS UPD_GB /*변경구분*/
--        FROM   TB_EP_DAUM X
--             , TB_EP_DAUM Y
--        WHERE  X.ID = Y.ID
--        AND    X.GB = 'CURR' /*현재데이터*/
--        AND    Y.GB = 'PREV' /*이전백업데이터*/
--        AND    (
--                (Y.GOODS_STAT != 'G008_70' AND X.GOODS_STAT = 'G008_70')
--                OR
--                (Y.SELF_MALL_YN = 'Y' AND X.SELF_MALL_YN = 'N')
--                OR
--                (Y.SELL_EDDT != X.SELL_EDDT AND Y.SELL_EDDT < NOW())
--               )
       ) X
;

-- 다음 EP 목록
SELECT IFNULL(MAPID,'')     AS MAPID
     , LPRICE
     , PRICE
     , MPRIC
     , IFNULL(PNAME,'')     AS PNAME
     , IFNULL(PGURL,'')     AS PGURL
     , IFNULL(IGURL,'')     AS IGURL
     , IFNULL(UPIMG,'')     AS UPIMG
     , IFNULL(CATE1,'')     AS CATE1
     , IFNULL(CAID1,'')     AS CAID1
     , IFNULL(CATE2,'')     AS CATE2
     , IFNULL(CAID2,'')     AS CAID2
     , IFNULL(CATE3,'')     AS CATE3
     , IFNULL(CAID3,'')     AS CAID3
     , IFNULL(CATE4,'')     AS CATE4
     , IFNULL(CAID4,'')     AS CAID4
     , IFNULL(MODEL,'')     AS MODEL
     , IFNULL(BRAND,'')     AS BRAND
     , IFNULL(MAKER,'')     AS MAKER
     , IFNULL(COUPON,'')    AS COUPON
     , IFNULL(MCOUPON,'')   AS MCOUPON
     , IFNULL(PCARD,'')     AS PCARD
     , IFNULL(POINT,'')     AS POINT
     , IFNULL(DELIV,'')     AS DELIV
     , IFNULL(DELIVTERM,'') AS DELIVTERM
     , IFNULL(RATING,'')    AS RATING
     , IFNULL(REVCT,'')     AS REVCT
     , IFNULL(EVENT,'')     AS EVENT
     , IFNULL(CARDDN,'')    AS CARDDN
     , IFNULL(CARDP,'')     AS CARDP
     , IFNULL(ADULT,'')     AS ADULT
     , IFNULL(PUBDATE,'')   AS PUBDATE
FROM   TB_EP_DAUM
WHERE  GB = 'LAST'
;

-- 다음 상품평EP 목록
SELECT R.GOODS_CD                           AS MAPID    /*상품ID*/
     , R.REVIEW_SQ                          AS REVIEWID /*상품평ID*/
     , CASE WHEN R.DISP_YN = 'N' OR R.DEL_YN = 'Y' THEN 'D'
            ELSE 'S'
       END                                  AS STATUS   /*상품평상태(S:정상, D:삭제)*/
     , R.REVIEW_TITLE                       AS TITLE    /*상품평제목*/
     , SUBSTRING(R.REVIEW_CONTENT,1,250)    AS CONTENT  /*상품평내용(250자이내)*/
     , C.CUST_NM                            AS WRITER
     , DATE_FORMAT(R.REG_DT,'%Y%m%d%H%i%S') AS CDATE /*상품평작성일시*/
     , R.SCORE                              AS RATING /*상품평점수*/
FROM   TB_REVIEW R
     , TB_CUSTOMER C
WHERE  R.REG_NO = C.CUST_NO
AND    R.CONFIRM_YN = 'Y' /*컨펌된넘*/
;