[DB] LISTAGG 마스터하기

SELECT A.MENU_ID, A.MENU_NAME, A.MENU_PRICE, A.TO_CHAR(MENU_DATE,'YYYY/MM/DD'), A.MENU_TEXT, A.MENU_TYPE, B_MENU_ID
FROM MENU A
INNER JOIN MATERIAL B
ON(A.MENU_ID = B.MENU_ID);



SELECT MENU_ID, LISTAGG(BUYERPROD_ID, ',')
WITHIN GROUP(ORDER BY BUYERPROD_ID) AS BUYERPROD_ID
FROM MATERIAL group by MENU_ID;

-- 1. 일단 재료ID들을 한국어로 바꾸자
SELECT B.MENU_ID, A.LGU_NAME
FROM LGU A
INNER JOIN MATERIAL B
ON(A.LGU_ID = B.BUYERPROD_ID);

-- 2. 메뉴와 재료가 짝지어졌으니 메뉴당 재료를 콤마로 구분해서 넣어야됨.......
SELECT B.MENU_ID, LISTAGG(A.LGU_NAME,',')
WITHIN GROUP (ORDER BY A.LGU_NAME) AS LGU_NAME
FROM LGU A
INNER JOIN MATERIAL B
ON(A.LGU_ID=B.BUYERPROD_ID) group by B.MENU_ID;

-- 이제 메뉴 LIST읽을때 한번에 읽어오게 하고싶음....

	SELECT A.MENU_ID, A.MENU_NAME, A.MENU_PRICE, TO_CHAR(A.MENU_DATE,'YYYY/MM/DD'),
        A.MENU_TEXT, A.MENU_TYPE, 
        LISTAGG(C.LGU_NAME,',') WITHIN GROUP (ORDER BY C.LGU_NAME)
    FROM MENU A, MATERIAL B, LGU C
    WHERE A.MENU_ID=B.MENU_ID AND B.BUYERPROD_ID= C.LGU_ID 
    GROUP BY A.MENU_ID, A.MENU_NAME, A.MENU_PRICE, A.MENU_TEXT, A.MENU_TYPE, 
            A.MENU_DATE, TO_CHAR(A.MENU_DATE,'YYYY/MM/DD')
    ORDER BY A.MENU_ID DESC;