我有一个有n列和x行的表格。
我想要查询显示一个包含所有列的表以及该列的最大长度值(以及具有最大值长度的那一行的行ID)。
+--------+--------+----+
| column | length | id |
+--------+--------+----+
| a | 123 | 1 |
| b | 123 | 8 |
| c | 123 | 6 |
| d | 123 | 5 |
| e | 123 | 3 |
+--------+--------+----+a,b,c等是列名,长度是该列中的最大长度,id是具有该列的最大长度的行。
像这样?但后来动态地为所有列:
SELECT MAX(LENGTH(`column_a`)) AS `length`, `id`
FROM `table` GROUP BY LENGTH(`column_a`)
ORDER BY LENGTH(`column_a`) DESC LIMIT 1像添加了两列的下一个查询(最大长度和行ID)
SHOW COLUMNS FROM `table`也许也可以使用此查询来获取特定表的列名称:
SELECT `column_name` AS `column`
FROM `information_schema`.`columns`
WHERE `table_name` = 'table'
AND `column_name` != 'id'
ORDER BY `ordinal_position`几乎在那里(感谢Bill)...(只需指定'table'),但现在如何运行_SQL al同样的运行...
SELECT CONCAT(GROUP_CONCAT(CONCAT('(SELECT \'', `column_name`,'\' AS `column`, LENGTH(`', `column_name`,'`) AS `length`, id ', 'FROM `', `table_schema`,'`.`', `table_name`,'` ORDER BY `length` DESC LIMIT 1)') SEPARATOR ' UNION ALL '), ';') AS _SQL
FROM `information_schema`.`columns`
WHERE `table_name` = 'table'
AND `column_name` IN (
SELECT `column_name`
FROM `information_schema`.`columns`
WHERE `table_name` = 'table'
AND `column_name` != 'id'
ORDER BY `ordinal_position`);