MySQL mysql选择动态行值作为列名，另一列作为值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10925445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql select dynamic row values as column names, another column as value
提问by gingerCodeNinja
I have a legacy table of user information (that is still in active use) and I cannot change the structure of -
我有一个旧的用户信息表（仍在使用中）并且我无法更改 -
id name value
------------------------------
0 timezone Europe/London
0 language en
0 country 45
0 something x
1 timezone Europe/Paris
1 language fr
1 country 46
timezone/language/country etc are only examples of names, they can be variable/there is no finite list other than unique on rows of that column
时区/语言/国家等只是名称的例子，它们可以是可变的/除了该列的行上唯一之外没有其他有限列表
I need a MySQL compatible SQL query that would return -
我需要一个与 MySQL 兼容的 SQL 查询，它会返回 -
id timezone language country something
---------------------------------------------------
0 Europe/London en 45 x
1 Europe/Paris fr 46
I've looked through various answers on stackoverflow around hacking Pivot table functionality in to MySQL, and similar but none of them seem to match this case of using variable column name alias from unique row values from a column of the same table. Although I have had little sleep so they're all starting to become a bit of a blur, apologies in advance.
我已经查看了有关将数据透视表功能入侵到 MySQL 的 stackoverflow 上的各种答案，类似但没有一个似乎与使用来自同一表的列的唯一行值的变量列名别名的情况相匹配。虽然我睡得很少，所以他们都开始变得有点模糊，提前道歉。
Closest I could find would be to use prepared statements https://stackoverflow.com/a/986088/830171which would first get all possible/unique values from name column and build a query that uses CASE WHEN, and/or multiple sub-SELECTor JOINon same table queries.
我能找到的最接近的是使用准备好的语句https://stackoverflow.com/a/986088/830171，它首先从 name 列中获取所有可能/唯一的值，并构建一个使用CASE WHEN, 和/或多个 sub-SELECT或JOINon 的查询相同的表查询。
The alternatives I can think of would be to get all rows for that user id and process them in the application itself in a for-loop, or attempt to limit the names to a finite amount and use sub-SELECTs/JOINs. However that second option is not ideal if a new name is added I'd have to revisit this query.
我能想到的替代方法是获取该用户 id 的所有行并在应用程序本身的 for 循环中处理它们，或者尝试将名称限制为有限数量并使用 sub- SELECTs/ JOINs。但是，如果添加了新名称，则第二个选项并不理想，我必须重新访问此查询。
Please tell me I've missed something obvious
请告诉我我错过了一些明显的东西
回答by eggyal
Unlike some other RDBMS MySQL doesn't have native support for pivoting operations of this sort by design(the developers feel it's more suited to the presentation, rather than database, layer of your application).
与其他一些 RDBMS 不同，MySQL 在设计上没有对这种类型的透视操作的本机支持（开发人员认为它更适合应用程序的表示层，而不是数据库层）。
If you absolutely must perfom such manipulations within MySQL, building a prepared statement is the way to go—although rather than messing around with CASE, I'd probably just use MySQL's GROUP_CONCAT()function:
如果你绝对必须在 MySQL 中执行这样的操作，构建一个准备好的语句是要走的路——尽管CASE我可能只是使用 MySQL 的GROUP_CONCAT()函数而不是搞乱：
SELECT CONCAT(
'SELECT `table`.id', GROUP_CONCAT('
, `t_', REPLACE(name, '`', '``'), '`.value
AS `', REPLACE(name, '`', '``'), '`'
SEPARATOR ''),
' FROM `table` ', GROUP_CONCAT('
LEFT JOIN `table` AS `t_', REPLACE(name, '`', '``'), '`
ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
SEPARATOR ''),
' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;
PREPARE stmt FROM @qry;
EXECUTE stmt;
See it on sqlfiddle.
Note that the result of GROUP_CONCAT()is limited by the group_concat_max_lenvariable (default of 1024 bytes: unlikely to be relevant here unless you have some extremely long namevalues).
请注意，结果GROUP_CONCAT()受group_concat_max_len变量限制（默认为 1024 字节：除非您有一些非常长的name值，否则在这里不太可能相关）。
