-- MariaDB 10.4 에서 Test -- WITH tablea AS ( SELECT 1 no, '7,8,9' aa UNION ALL SELECT 2, '8,10,1' ) , tableb AS ( SELECT 1 no, 7 idx, '사과' idxnm UNION ALL SELECT 2, 8, '귤' UNION ALL SELECT 3, 9, '포도' UNION ALL SELECT 4, 1, '배' UNION ALL SELECT 5, 10, '레몬' ) SELECT a.no , a.aa , GROUP_CONCAT(b.idxnm ORDER BY INSTR(CONCAT(',', a.aa, ','), CONCAT(',', b.idx, ','))) bb FROM tablea a INNER JOIN tableb b ON INSTR(CONCAT(',', a.aa, ','), CONCAT(',', b.idx, ',')) > 0 GROUP BY a.no, a.aa ;
아! 제가 질문을 잘못 보고 답변 드렸네요.
원본이 '7,8,9' 가 아니었군요. '7,8,9'도 group_concat 의 결과였네요?
단순 idx 조인후 idxnm 을 group_concat 하면 되는 거였네요.
-- MariaDB 10.4 에서 Test -- WITH tablea AS ( SELECT 1 no, 7 idx UNION ALL SELECT 1, 8 UNION ALL SELECT 1, 9 UNION ALL SELECT 2, 8 UNION ALL SELECT 2, 10 UNION ALL SELECT 2, 1 ) , tableb AS ( SELECT 1 no, 7 idx, '사과' idxnm UNION ALL SELECT 2, 8, '귤' UNION ALL SELECT 3, 9, '포도' UNION ALL SELECT 4, 1, '배' UNION ALL SELECT 5, 10, '레몬' ) SELECT a.no , GROUP_CONCAT(a.idx ORDER BY a.idx) aa , GROUP_CONCAT(b.idxnm ORDER BY a.idx) bb FROM tablea a INNER JOIN tableb b ON a.idx = b.idx GROUP BY a.no ;