目录
查询表所有的字段名/列名 进行a-z排序
#
# 查询表的所有字段,并进行a-z排序
# 增加GROUP BY COLUMN_NAME 或者 WHERE TABLE_SCHEME='sys_city' 可过滤掉其它库中存在的相同表名
#
SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'sys_city'
-- GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME ASC
查询数据库的表数据
SELECT * FROM sys_city;
使用GROUP_CONCAT函数,把行变成列,并用分隔符','进行拼接
#
# 使用GROUP_CONCAT函数,把行变成列,并用分隔符','进行拼接
#
SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME ORDER BY COLUMN_NAME ASC SEPARATOR ',') AS 'sort_column'
FROM information_schema.COLUMNS WHERE TABLE_NAME = 'sys_city'
GROUP BY TABLE_NAME;
输出结果:
address,city_name,code,full_name,id,id_nodes,latlng,level,name,parent_id,pinyin,postal_code,province_name,town_name
#
# 使用GROUP_CONCAT函数,把行变成列,并用分隔符','进行拼接
# 使用FIELD函数,把指定的列名 排序在最前面
#
SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME ORDER BY FIELD(COLUMN_NAME,'id') DESC ,COLUMN_NAME ASC SEPARATOR ',') AS 'sort_column'
FROM information_schema.COLUMNS WHERE TABLE_NAME = 'sys_city' AND TABLE_SCHEMA='lvyuev2'
GROUP BY TABLE_NAME;
##
## 输出结果: id,address,city_name,code,full_name,id_nodes,latlng,level,name,parent_id,pinyin,postal_code,province_name,town_name
##
方式一: 复制排序后的列名,进行查询
SELECT id,address,city_name,code,full_name,id_nodes,latlng,level,name,parent_id,pinyin,postal_code,province_name,town_name
FROM sys_city;
方式二: 使用存储过程,进行查询
SET @col := (SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME ORDER BY FIELD(COLUMN_NAME,'id') DESC ,COLUMN_NAME ASC SEPARATOR ',') AS 'sort_column'
FROM information_schema.COLUMNS WHERE TABLE_NAME = 'sys_city'
GROUP BY TABLE_NAME);
SET @sql := CONCAT('SELECT ' ,@col, ' FROM sys_city' , ' LIMIT 10' );
PREPARE stmt FROM @sql;
EXECUTE stmt;