代码实例

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 '01' 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 '01' stored as textfile location 'ZZZZZZZ/JOB_ANALYZE_LOG';"                        )            END        ) AS statement    ,ORDINAL_POSITION    ,maxcol    ,mincolFROM (    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    ) cWHERE table_schema = 'test'