style24.doc/산출물/3.설계/03.인터페이스/검색엔진/검색_상품색인_org.sql

-- SELECT * FROM (
SELECT DISTINCT  G.GOODS_CD
     , IF(G.SELF_GOODS_YN  = 'Y', O.OPT_CD1, G.MAIN_COLOR_CD) AS COLOR_CD
     , (SELECT GROUP_CONCAT(DISTINCT CONCAT(T.OPT_CD2,'|',T.SOLDOUT_YN, '|', VS.CURR_STOCK_QTY)) FROM TB_OPTION T 
                                                                         INNER JOIN VW_STOCK VS ON T.GOODS_CD = VS.GOODS_CD 
                                                                                                AND T.OPT_CD  = VS.OPT_CD 
                                                                         WHERE T.DISP_YN  = 'Y' 
                                                                         AND T.GOODS_CD = G.GOODS_CD 
                                                                         AND T.OPT_CD1 = O.OPT_CD1  ) AS SIZE_INFO
     , I.SIZE_GB 
     , (CASE WHEN I.SIZE_GB = 'T' THEN '상의'
             WHEN I.SIZE_GB = 'B' THEN '하의'
             WHEN I.SIZE_GB = 'S' THEN '신발'
             ELSE '' END
              ) AS SIZE_GB_NM 
     , (SELECT GROUP_CONCAT(DISTINCT CONCAT(T.OPT_CD1, '|',C.COLOR_FILE) ) FROM TB_OPTION T 
                                                                           INNER JOIN TB_COLOR C ON T.OPT_CD1 = C.COLOR_CD 
                                                                           WHERE T.GOODS_CD = G.GOODS_CD 
                                                                           AND T.DISP_YN  = 'Y')  AS COLOR_INFO
     , G.GOODS_NM
     , G.GOODS_TNM
     , CONCAT(IF(IFNULL(G.GOODS_GB,'') IN ('G073_11','G073_15', ''), '',IFNULL(CONCAT('[',FN_GET_CODE_NM('G073',G.GOODS_GB),']'),'') )
             ,IF(G.FOREIGN_BUY_YN='Y','[해외구매대행]','' )
             ,IF(G.PARALLEL_IMPORT_YN = 'Y','[병행수입]','')
             ,IF(G.ORDER_MADE_YN='Y','[주문제작]','')
             ,G.GOODS_NM ) AS GOODS_FULL_NM
     , G.GOODS_SNM        
     , G.BRAND_CD
     , (CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM ELSE BG.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
     , BG.BRAND_GROUP_NO
     , G.ITEMKIND_CD
     , G.FORMAL_GB
     , G.LIST_PRICE
     , G.CURR_PRICE
     , BP.PC_CURR_PRICE
     , BP.MO_CURR_PRICE
     , BP.APP_CURR_PRICE
     -- , BP.CURR_PRICE
     -- , 100 - ROUND((BP.CURR_PRICE / G.LIST_PRICE) * 100 ,0) AS DC_RATE
     , G.SELF_GOODS_YN
     , G.GOODS_STAT
     , G.PNT_PRATE
     , G.PRE_PPNT_USABLE_YN
     , G.PNT_MRATE
     , G.PRE_MPNT_USABLE_YN
     -- , (CASE WHEN  'P' /**P*/ = 'P' THEN G.PNT_PRATE ELSE G.PNT_MRATE END) AS PNT_RATE
     -- , (CASE WHEN  'P' /**P*/ = 'P' THEN G.PRE_PPNT_USABLE_YN ELSE G.PRE_MPNT_USABLE_YN END) AS PRE_PNT_USABLE_YN
     , G.MIN_ORD_QTY
     , G.MAX_ORD_QTY
     , G.DAY_MAX_ORD_QTY
     , G.SEX_GB
     , FN_GET_CODE_NM('G007',G.SEX_GB) AS SEX_NM
     , E.DELV_FEE
     , E.MIN_ORD_AMT
     , G.GOODS_GB
     , (SELECT MAX(NI_CLSF_CD) FROM tb_goods_noti_info  WHERE GOODS_CD = G.GOODS_CD) AS NI_CLSF_CD
     , G.MAIN_COLOR_CD
     , G.SUPPLY_COMP_CD
     , G.SELF_MALL_YN
     , G.AGE_GRP_CD
     , FN_GET_CODE_NM('G023',G.AGE_GRP_CD) AGE_GRP_NM
     , G.SEASON_CD 
     , FN_GET_CODE_NM('G006',G.SEASON_CD) SEASON_NM
     -- , G.GIFT_PACK_YN
     -- , G.NEW_CUST_ORD_YN
     -- , G.ADULT_YN
     , G.GOODS_TYPE
     -- , G.CHANGEABLE_YN
     -- , G.RETURNABLE_YN
     -- , (SELECT CATE1_NO FROM TB_SITE_BRAND WHERE  BRAND_CD = G.BRAND_CD AND SITE_CD =  'G000_10' AND USE_YN = 'Y') AS BRAND_CATE1_NO
     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IF(G.SELF_GOODS_YN  = 'Y' AND G.GOODS_TYPE ='G056_N', O.OPT_CD1, G.MAIN_COLOR_CD) AND DEFAULT_IMG_YN = 'Y') AS SYS_IMG_NM
     , (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IF(G.SELF_GOODS_YN  = 'Y' AND G.GOODS_TYPE ='G056_N', O.OPT_CD1, G.MAIN_COLOR_CD) AND MOUSEOVER_IMG_YN = 'Y') AS SYS_IMG_NM2
--     , (CASE WHEN W.GOODS_CD IS NULL THEN 'N' ELSE 'Y' END) AS WISH_YN  -- 실시간 데이터 반영? 
     , (CASE WHEN G.GOODS_TYPE = 'G056_N' THEN (SELECT IFNULL(MAX(CASE WHEN SOLDOUT_YN = 'Y' THEN 0
                                                               ELSE CURR_STOCK_QTY
                                                               END) ,0)
                                                FROM  VW_STOCK
                                                WHERE GOODS_CD = G.GOODS_CD
                                                AND OPT_CD1 =  (CASE WHEN G.SELF_GOODS_YN = 'N' THEN OPT_CD1 
                                                                     ELSE  IFNULL(G.MAIN_COLOR_CD,'XX') END)
                                                GROUP BY GOODS_CD )
            ELSE (SELECT IFNULL(MAX(CASE WHEN SOLDOUT_YN = 'Y' THEN 0
                                      ELSE CURR_STOCK_QTY
                                      END) ,0)
                  FROM  VW_STOCK_COMPOSE
                  WHERE GOODS_CD= G.GOODS_CD
                  GROUP BY GOODS_CD )
            END) AS STOCK_QTY
       , K.AD_KEYWORD
       , (SELECT GROUP_CONCAT(DISTINCT CONCAT(C4.LEAF_CATE_NO , '|', C4.FULL_CATE_NM ) ) FROM TB_CATE_4SRCH C4
                                                                                         INNER JOIN TB_CATE_GOODS CG ON C4.LEAF_CATE_NO = CG.CATE_NO 
                                                                                         WHERE  C4.SITE_CD = 'G000_10'
                                                                                         AND C4.CATE_TYPE = 'G031_10' -- 상품타입 
                                                                                         AND C4.CATE_GB  = 'G032_101' -- 
                                                                                         AND CG.GOODS_CD = G.GOODS_CD)  AS CATE_INFO
        , G.REG_DT AS REG_DT 
        , GS.SELL_WEEK_QTY AS ORDER_CNT
        , GS.REVIEW_REG_CNT AS REVIEW_CNT
        , (CASE WHEN G.SELF_GOODS_YN ='Y' AND ifnull(QDS.GOODS_CD, 'Y') THEN 'Y' ELSE 'N' END ) AS QUIK_DELV_YN
      -- , FN_IS_GOODS_COUPON('P',G.GOODS_CD, BP.CURR_PRICE) AS COUPON_PC_ICON  -- 속도 이슈
      -- , FN_IS_GOODS_COUPON('M',G.GOODS_CD, BP.CURR_PRICE) AS COUPON_MO_ICON
      -- , FN_IS_GOODS_COUPON('A',G.GOODS_CD, BP.CURR_PRICE) AS COUPON_APP_ICON
      -- , (CASE WHEN BP.CURR_PRICE <= E.MIN_ORD_AMT THEN 'Y' ELSE 'N' END) AS DELV_FREE_ICON
      -- , FN_GET_FREEGIFT_GOODS_YN(G.GOODS_CD) AS FREEGIFT_ICON
      -- , (CASE WHEN G.FORMAL_GB = 'G009_10' THEN 'Y' ELSE 'N' END) AS NEW_GOODS_ICON
FROM TB_GOODS G
INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
                      AND B.USE_YN = 'Y'
INNER JOIN TB_BRAND_GROUP BG ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
                            AND BG.USE_YN = 'Y'                            
-- INNER JOIN (SELECT GOODS_CD
--                 , FN_GET_BENEFIT_PRICE( 'P' /**P*/,GOODS_CD, CURR_PRICE, '00' /**P*/) AS CURR_PRICE 
--            FROM TB_GOODS
--            ) BP ON G.GOODS_CD = BP.GOODS_CD
INNER JOIN TB_DELV_FEE_POLICY E ON G.SUPPLY_COMP_CD = E.SUPPLY_COMP_CD
                                AND G.DELV_FEE_CD =  E.DELV_FEE_CD
INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
INNER JOIN TB_OPTION O ON G.GOODS_CD  = O.GOODS_CD
                            AND O.DISP_YN = 'Y'
LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE BP ON G.GOODS_CD = BP.GOODS_CD 
                                          AND BP.CUST_GB = 'G100_00'
LEFT OUTER JOIN (SELECT AKG.GOODS_CD
                      , GROUP_CONCAT(AK.AD_KEYWORD) AS  AD_KEYWORD
                 FROM TB_AD_KEYWORD AK
                 INNER JOIN TB_AD_KEYWORD_GOODS AKG ON AK.AD_KEYWORD_SQ  = AKG.AD_KEYWORD_SQ 
                 WHERE NOW() BETWEEN  AK.AD_KEYWORD_STDT AND AK.AD_KEYWORD_EDDT
                 AND AK.USE_YN  = 'Y'
                 GROUP BY  AKG.GOODS_CD
                ) K ON G.GOODS_CD = K.GOODS_CD 
LEFT OUTER JOIN (SELECT MAX(GOODS_CD) AS GOODS_CD 
                         , MAX(CASE WHEN  RNUM = 1 THEN  VIDEO_GB END) AS  VIDEO_GB_M
                         , MAX(CASE WHEN  RNUM = 1 THEN  VIDEO_VAL END) AS  VIDEO_VAL_M
                         , MAX(CASE WHEN  RNUM = 2 THEN  VIDEO_GB END) AS  VIDEO_GB_S
                         , MAX(CASE WHEN  RNUM = 2 THEN  VIDEO_VAL END) AS  VIDEO_VAL_S
                   FROM (
                         SELECT A.DISPLOC_VAL AS GOODS_CD
                              , B.VIDEO_GB 
                              , B.VIDEO_VAL 
                              , A.REG_DT 
                              , RANK() OVER(PARTITION BY A.DISPLOC_VAL ORDER BY A.REG_DT ) RNUM 
                         FROM TB_VIDEO_DISPLOC  A
                         INNER JOIN  TB_VIDEO B ON A.VIDEO_SQ = B.VIDEO_SQ 
                                                AND B.DISP_YN ='Y'
                         WHERE A.DISPLOC_GB ='G'
                         AND A.DISP_YN = 'Y'
                        ) V
                   ) V ON G.GOODS_CD = V.GOODS_CD 
LEFT OUTER JOIN TB_ITEMKIND I ON G.ITEMKIND_CD  = I.ITEMKIND_CD 
-- LEFT OUTER JOIN TB_WISHLIST W ON G.GOODS_CD = W.GOODS_CD
--                               AND IFNULL( 0 /**P*/, 0) = W.CUST_NO
LEFT OUTER JOIN TB_GOODS_SHOT_DELV_SKIP QDS ON G.GOODS_CD  = QDS.GOODS_CD 
WHERE 1 = 1                              
-- AND G.GOODS_CD =  '14443216' /**P*/  -- 테스트용   입점:STYS00000042 , 자사:14443216 , 딜 : STYD000000025 , 세트 : STYS00000042
AND G.SELF_MALL_YN = 'Y' -- 자사몰 노출(기획전과 상품상세는 노출)
-- AND G.SELF_GOODS_YN ='Y'  -- 테스트용
AND G.GOODS_STAT = 'G008_90'
-- ) Z
-- WHERE 1 = 1
-- AND Z.AD_KEYWORD IS NOT NULL
;


TRUNCATE TABLE TMP_SEARCH_ENGINE;

CREATE TABLE TMP_SEARCH_ENGINE AS
WITH TAB_GOODS AS (
	SELECT G.GOODS_CD
	     , G.GOODS_NM
	     , G.GOODS_TNM
	     , G.GOODS_GB
	     , G.FOREIGN_BUY_YN
	     , G.PARALLEL_IMPORT_YN
	     , G.ORDER_MADE_YN
	     , G.GOODS_SNM        
	     , G.BRAND_CD
	     , G.ITEMKIND_CD
	     , G.FORMAL_GB
	     , G.LIST_PRICE
	     , G.CURR_PRICE
	     , G.SELF_GOODS_YN
	     , G.GOODS_STAT
	     , G.PNT_PRATE
	     , G.PRE_PPNT_USABLE_YN
	     , G.PNT_MRATE
	     , G.PRE_MPNT_USABLE_YN
	     , G.MIN_ORD_QTY
	     , G.MAX_ORD_QTY
	     , G.DAY_MAX_ORD_QTY
	     , G.SEX_GB
	     , G.MAIN_COLOR_CD
	     , G.SUPPLY_COMP_CD
	     , G.SELF_MALL_YN
	     , G.AGE_GRP_CD
	     , G.SEASON_CD 
	     , G.GOODS_TYPE
	     , G.REG_DT AS REG_DT 
	FROM   TB_GOODS G
), TAB_OPT_SIZE AS (
    /*옵션 사이즈 목록 조회*/
    /*자사상품*/
    SELECT G.GOODS_CD
         , O.OPT_CD
         , O.OPT_CD1
         , C.COLOR_FILE /*RGB값*/
         , GROUP_CONCAT(CONCAT(O.OPT_CD2,'|',O.SOLDOUT_YN,'|',VS.CURR_STOCK_QTY)) AS SIZE_INFO
    FROM   TAB_GOODS G
         , TB_OPTION O
         , TB_COLOR C
         , VW_STOCK VS
    WHERE  G.GOODS_CD = O.GOODS_CD
    AND    O.OPT_CD1 = C.COLOR_CD
    AND    O.GOODS_CD = VS.GOODS_CD
    AND    O.OPT_CD = VS.OPT_CD
    AND    G.SELF_GOODS_YN = 'Y' /*자사상품*/
    AND    O.DISP_YN = 'Y'
    AND    C.USE_YN = 'Y'
    GROUP  BY G.GOODS_CD, O.OPT_CD, O.OPT_CD1, C.COLOR_FILE
    UNION ALL
    /*입점상품*/
    SELECT G.GOODS_CD
         , O.OPT_CD
         , G.MAIN_COLOR_CD AS OPT_CD1
         , NULL            AS COLOR_FILE /*RGB값*/
         , GROUP_CONCAT(CONCAT(O.OPT_CD2,'|',O.SOLDOUT_YN,'|',VS.CURR_STOCK_QTY)) AS SIZE_INFO
    FROM   TAB_GOODS G
         , TB_OPTION O
         , VW_STOCK VS
    WHERE  G.GOODS_CD = O.GOODS_CD
    AND    O.GOODS_CD = VS.GOODS_CD
    AND    O.OPT_CD = VS.OPT_CD
    AND    G.SELF_GOODS_YN = 'N' /*입점상품*/
    AND    O.DISP_YN = 'Y'
    GROUP  BY G.GOODS_CD, O.OPT_CD, G.MAIN_COLOR_CD
), TAB_OPT AS (
    /*옵션 목록*/
    SELECT OS.GOODS_CD
         , GROUP_CONCAT(OS.SIZE_INFO ORDER BY OS.SIZE_INFO) AS SIZES
    FROM   TAB_OPT_SIZE OS
    GROUP  BY OS.GOODS_CD, OS.OPT_CD1
), TAB_CPN_ICON AS (
    /*상품별 쿠폰 아이콘 목록*/
    SELECT G.GOODS_CD
         , FN_IS_GOODS_COUPON('P',G.GOODS_CD,GBP.PC_CURR_PRICE)  AS CPN_PC_ICON  -- 속도 이슈
         , FN_IS_GOODS_COUPON('M',G.GOODS_CD,GBP.MO_CURR_PRICE)  AS CPN_MO_ICON
         , FN_IS_GOODS_COUPON('A',G.GOODS_CD,GBP.APP_CURR_PRICE) AS CPN_APP_ICON
         , FN_GET_FREEGIFT_GOODS_YN(G.GOODS_CD)                  AS FREEGIFT_ICON
    FROM   TAB_GOODS G
         , TB_GOODS_BENEFIT_PRICE GBP
    WHERE  G.GOODS_CD = GBP.GOODS_CD
    AND    GBP.CUST_GB = 'G100_00'
)
SELECT G.*
     , O.SIZES
     , CI.CPN_PC_ICON
     , CI.CPN_MO_ICON
     , CI.CPN_APP_ICON
     , CI.FREEGIFT_ICON
     , I.SIZE_GB
     , CASE WHEN I.SIZE_GB = 'T' THEN '상의'
            WHEN I.SIZE_GB = 'B' THEN '하의'
            WHEN I.SIZE_GB = 'S' THEN '신발'
            ELSE NULL
       END       AS SIZE_GB_NM
FROM   TAB_GOODS G
     , TAB_OPT O
     , TAB_CPN_ICON CI
     , TB_ITEMKIND I
WHERE  G.GOODS_CD = O.GOODS_CD
AND    G.GOODS_CD = CI.GOODS_CD
AND    G.ITEMKIND_CD = I.ITEMKIND_CD
-- AND    G.SELF_GOODS_YN = 'Y'
-- AND    G.GOODS_CD = '14373685'
;