代码实例

SELECT table_schema
    ,table_name
    ,(
        CASE 
            WHEN ORDINAL_POSITION = mincol
                AND ORDINAL_POSITION < maxcol
                THEN CONCAT (
                        "create  table if not exists "
                        ,table_schema
                        ,"."
                        ,table_name
                        ,"(`"
                        ,column_name
                        ,"` string,"
                        )
            WHEN ORDINAL_POSITION = mincol
                AND ORDINAL_POSITION = maxcol
                THEN CONCAT (
                        "create  table if not exists "
                        ,table_schema
                        ,"."
                        ,table_name
                        ,"(`"
                        ,column_name
                        ,"` string) row format DELIMITED FIELDS terminated by '\001' stored as textfile location 'ZZZZZZZ/JOB_ANALYZE_LOG';"
                        )
            WHEN ORDINAL_POSITION > mincol
                AND ORDINAL_POSITION < maxcol
                THEN CONCAT (
                        "`"
                        ,column_name
                        ,"` string,"
                        )
            WHEN ORDINAL_POSITION = maxcol
                THEN CONCAT (
                        "`"
                        ,column_name
                        ,"` string ) row format DELIMITED FIELDS terminated by '\001' stored as textfile location 'ZZZZZZZ/JOB_ANALYZE_LOG';"
                        )
            END
        ) AS statement
    ,ORDINAL_POSITION
    ,maxcol
    ,mincol
FROM (
    SELECT b.*
        ,a.maxcol
        ,a.mincol
    FROM (
        SELECT table_schema
            ,table_name
            ,max(ORDINAL_POSITION) maxcol
            ,min(ORDINAL_POSITION) mincol
        FROM information_schema.COLUMNS
        GROUP BY table_schema
            ,table_name
        ) a
    JOIN (
        SELECT table_schema
            ,table_name
            ,ORDINAL_POSITION
            ,column_name
            ,COLUMN_TYPE
        FROM information_schema.COLUMNS
        ORDER BY table_schema
            ,table_name
            ,ORDINAL_POSITION ASC
        ) b ON a.table_schema = b.table_schema
        AND a.table_name = b.table_name
    ) c
WHERE table_schema = 'test'