쿼리 참고용

SELECT
    ci.table_name AS table_type,
    CONCAT('[', GROUP_CONCAT('"', ci.column_name, '"'), ']') AS field_list,
    CONCAT(
   '[',
        (SELECT CONCAT(
            '"', MIN(vi.year), '",',
            '"', MIN(vi.month), '",',
            '"', MIN(vi.day), '"'
        ) FROM value_info vi WHERE vi.table_name = ci.table_name),
        ']'
    ) AS value_list,
        CONCAT(
        '[',
        (SELECT GROUP_CONCAT('"', ti.time_column, '"')
        FROM time_info ti WHERE ti.table_name = ci.table_name),
        ']'
    ) AS time_list,
     CONCAT(
        '[',
        (SELECT CONCAT(
            '"', MIN(tv.year), '",',
            '"', MIN(tv.month), '"'
        ) FROM time_value tv WHERE tv.table_name = ci.table_name),
        ']'
    ) AS time_value
FROM
    column_info ci
where ci.key_type = 'pk'
GROUP BY
    ci.table_name;

concat 안에  서브쿼리 사용, where 조건문 left table의 key 값  = 서브쿼리에 사용된 테이블의 key 값 

 

아래는 postgresql ver

SELECT
    ci.table_name AS table_type,
    CONCAT('[', STRING_AGG('"', ci.column_name, '"'), ']') AS field_list,
    CONCAT(
        '[',
        (SELECT CONCAT(
            '"', MIN(vi.year), '",',
            '"', MIN(vi.month), '",',
            '"', MIN(vi.day), '"'
        ) FROM value_info vi WHERE vi.table_name = ci.table_name),
        ']'
    ) AS value_list,
    CONCAT(
        '[',
        (SELECT STRING_AGG('"', ti.time_column, '"')
        FROM time_info ti WHERE ti.table_name = ci.table_name),
        ']'
    ) AS time_list,
    CONCAT(
        '[',
        (SELECT CONCAT(
            '"', MIN(tv.year), '",',
            '"', MIN(tv.month), '"'
        ) FROM time_value tv WHERE tv.table_name = ci.table_name),
        ']'
    ) AS time_value
FROM
    column_info ci
WHERE
    ci.key_type = 'pk'
GROUP BY
    ci.table_name;