SELECT INFO_IDX, INFO_CATE, TITLE_KOR, TITLE_ENG, SUMMARY, WEBSITE, IMAGE_NAME, IMAGE_FORMAT, IMAGE_SIZE, IMAGE_PATH, HASH_TAG, HIT, TOP_YN, OPEN_YN, MANAGER_MEMO, CREATE_USER, DATE_FORMAT(CREATE_DATE, '%Y.%m.%d') CDATE, UPDATE_USER, DATE_FORMAT(UPDATE_DATE,'%Y.%m.%d') UDATE , FNC_CODE_NAME('K',INFO_CATE) INFO_CATE_NAME , (CASE WHEN INFO_CATE='C04001' THEN (SELECT COUNT(WINE_IDX) FROM WINE_MST WHERE WINERY_INFO_IDX=a.INFO_IDX AND STATE='Y' AND OPEN_YN='Y') WHEN INFO_CATE='C04002' THEN (SELECT COUNT(WINE_IDX) FROM WINE_MST WHERE AREA_INFO_IDX=a.INFO_IDX AND STATE='Y' AND OPEN_YN='Y') WHEN INFO_CATE='C04003' THEN (SELECT COUNT(DISTINCT t1.WINE_IDX) FROM WINE_GRAPE_VARIETY t1 INNER JOIN WINE_MST t2 ON t1.WINE_IDX=t2.WINE_IDX AND t2.STATE='Y' AND t2.OPEN_YN='Y' WHERE t1.STATE='Y' AND INFO_IDX=a.INFO_IDX) ELSE 0 END) WINE_CNT , (SELECT ALIKE_WORD FROM ALIKE_WORD WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) ALIKE_WORD , (CASE WHEN INFO_CATE='C04001' THEN (SELECT CONCAT(LINKAGE_IDX, '/', LINKAGE_KNAME, '/', LINKAGE_ENAME, '/', FLAG_PATH) FROM INFO_LINKAGE T1 LEFT JOIN PRODUCTION_AREA T2 ON T1.LINKAGE_IDX=T2.AREA_CODE AND T2.STATE='Y' WHERE T1.STATE='Y' AND T1.INFO_IDX=a.INFO_IDX LIMIT 1) WHEN INFO_CATE='C04002' THEN (SELECT (CASE WHEN SUBSTRING(LINKAGE_IDX,4,6)='000000' THEN FNC_AREA_NAME(1,'K',LINKAGE_IDX) WHEN SUBSTRING(LINKAGE_IDX,6,4)='0000' THEN CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX), '/', FNC_AREA_NAME(2,'K',LINKAGE_IDX)) WHEN SUBSTRING(LINKAGE_IDX,8,2)='00' THEN CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX), '/', FNC_AREA_NAME(2,'K',LINKAGE_IDX), '/', FNC_AREA_NAME(3,'K',LINKAGE_IDX)) ELSE CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX), '/', FNC_AREA_NAME(2,'K',LINKAGE_IDX), '/', FNC_AREA_NAME(3,'K',LINKAGE_IDX), '/', FNC_AREA_NAME(4,'K',LINKAGE_IDX)) END) FROM INFO_LINKAGE WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) WHEN INFO_CATE='C04003' OR INFO_CATE='C04006' THEN (SELECT GROUP_CONCAT(CONCAT(LINKAGE_KNAME, '/', LINKAGE_ENAME) ORDER BY LINKAGE_IDX ASC, INFO_SEQ ASC SEPARATOR '|') FROM INFO_LINKAGE WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) ELSE INFO_TYPE END) LINKAGE_SEARCH , (CASE WHEN INFO_CATE='C04001' THEN (SELECT CONCAT(LINKAGE_IDX, '|', LINKAGE_KNAME, '|', LINKAGE_ENAME, '|', FLAG_PATH) FROM INFO_LINKAGE T1 LEFT JOIN PRODUCTION_AREA T2 ON T1.LINKAGE_IDX=T2.AREA_CODE AND T2.STATE='Y' WHERE T1.STATE='Y' AND T1.INFO_IDX=a.INFO_IDX LIMIT 1) WHEN INFO_CATE='C04002' THEN (SELECT CONCAT(LINKAGE_IDX,'|',(CASE WHEN SUBSTRING(LINKAGE_IDX,4,6)='000000' THEN CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX)) WHEN SUBSTRING(LINKAGE_IDX,6,4)='0000' THEN CONCAT(CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX)), '/', CONCAT(FNC_AREA_NAME(2,'K',LINKAGE_IDX))) WHEN SUBSTRING(LINKAGE_IDX,8,2)='00' THEN CONCAT(CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX)), '/', CONCAT(FNC_AREA_NAME(2,'K',LINKAGE_IDX)), '/', CONCAT(FNC_AREA_NAME(3,'K',LINKAGE_IDX))) ELSE CONCAT(CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX)), '/', CONCAT(FNC_AREA_NAME(2,'K',LINKAGE_IDX)), '/', CONCAT(FNC_AREA_NAME(3,'K',LINKAGE_IDX)), '/', CONCAT(FNC_AREA_NAME(4,'K',LINKAGE_IDX))) END),'|',FNC_AREA_NAME(1,'FLAG',LINKAGE_IDX)) FROM INFO_LINKAGE WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) WHEN INFO_CATE='C04003' OR INFO_CATE='C04006' THEN (SELECT GROUP_CONCAT(CONCAT(LINKAGE_IDX, '/', FNC_CODE_NAME('K',LINKAGE_IDX), '/', LINKAGE_KNAME) ORDER BY LINKAGE_IDX ASC, INFO_SEQ ASC SEPARATOR '|') FROM INFO_LINKAGE WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) ELSE CONCAT(INFO_TYPE,'/',FNC_CODE_NAME('K',INFO_TYPE)) END) LINKAGE_NAME1 , (CASE WHEN INFO_CATE='C04001' THEN (SELECT CONCAT(LINKAGE_IDX, '|', LINKAGE_KNAME, '(', LINKAGE_ENAME ,')', '|', FLAG_PATH) FROM INFO_LINKAGE T1 LEFT JOIN PRODUCTION_AREA T2 ON T1.LINKAGE_IDX=T2.AREA_CODE AND T2.STATE='Y' WHERE T1.STATE='Y' AND T1.INFO_IDX=a.INFO_IDX LIMIT 1) WHEN INFO_CATE='C04002' THEN (SELECT CONCAT(LINKAGE_IDX,'|',(CASE WHEN SUBSTRING(LINKAGE_IDX,4,6)='000000' THEN CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(1,'E',LINKAGE_IDX),')') WHEN SUBSTRING(LINKAGE_IDX,6,4)='0000' THEN CONCAT(CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(1,'E',LINKAGE_IDX),')'), '/', CONCAT(FNC_AREA_NAME(2,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(2,'E',LINKAGE_IDX),')')) WHEN SUBSTRING(LINKAGE_IDX,8,2)='00' THEN CONCAT(CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(1,'E',LINKAGE_IDX),')'), '/', CONCAT(FNC_AREA_NAME(2,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(2,'E',LINKAGE_IDX),')'), '/', CONCAT(FNC_AREA_NAME(3,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(3,'E',LINKAGE_IDX),')')) ELSE CONCAT(CONCAT(FNC_AREA_NAME(1,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(1,'E',LINKAGE_IDX),')'), '/', CONCAT(FNC_AREA_NAME(2,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(2,'E',LINKAGE_IDX),')'), '/', CONCAT(FNC_AREA_NAME(3,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(3,'E',LINKAGE_IDX),')'), '/', CONCAT(FNC_AREA_NAME(4,'K',LINKAGE_IDX) ,'(', FNC_AREA_NAME(4,'E',LINKAGE_IDX),')')) END),'|',FNC_AREA_NAME(1,'FLAG',LINKAGE_IDX)) FROM INFO_LINKAGE WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) WHEN INFO_CATE='C04003' OR INFO_CATE='C04006' THEN (SELECT GROUP_CONCAT(CONCAT(LINKAGE_IDX, '/', FNC_CODE_NAME('K',LINKAGE_IDX), '/', LINKAGE_KNAME, '(', LINKAGE_ENAME, ')') ORDER BY LINKAGE_IDX ASC, INFO_SEQ ASC SEPARATOR '|') FROM INFO_LINKAGE WHERE STATE='Y' AND INFO_IDX=a.INFO_IDX) ELSE CONCAT(INFO_TYPE,'/',FNC_CODE_NAME('K',INFO_TYPE),'(',FNC_CODE_NAME('E',INFO_TYPE),')') END) LINKAGE_NAME2 , FNC_CODE_NAME('K',INFO_TYPE) INFO_TYPE_NAME FROM INFO_MST a WHERE STATE='Y' AND OPEN_YN='Y' AND (INFO_CATE NOT IN ('C04001', 'C04004', 'C04006', 'C04007') AND INFO_TYPE NOT IN ('C12004', 'C12005')) ORDER BY UPDATE_DATE DESC LIMIT 0, 10
와인사전 - 와인21닷컴
