MySQL没有UNPIVOT函数，但是您可以使用将列转换为行
UNIOn ALL。
基本语法为：
select id, word, qtyfrom( select id, 'abc' word, abc qty from yt where abc > 0 union all select id, 'brt', brt from yt where brt > 0) dorder by id;
对于您的情况，您声明需要动态列的解决方案。如果真是这样，那么您将需要使用准备好的语句来生成动态SQL：
SET @sql = NULL;SELECt GROUP_CONCAt(DISTINCT CONCAt( 'select id, ''', c.column_name, ''' as word, ', c.column_name, ' as qty from yt where ', c.column_name, ' > 0' ) SEPARATOR ' UNIOn ALL ' ) INTO @sqlFROM information_schema.columns cwhere c.table_name = 'yt' and c.column_name not in ('id')order by c.ordinal_position;SET @sql = CONCAt('select id, word, qtyfrom(', @sql, ') x order by id');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;参见带有演示的SQL Fiddle
