-- 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' ;