mysql选择动态行值作为列名，另一列作为值 [英] mysql select dynamic row values as column names, another column as value

问题描述

我有一个旧的用户信息表(仍在使用中)并且我无法更改 -

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

我需要一个可以返回的 MySQL 兼容 SQL 查询 -

I need a MySQL compatible SQL query that would return -

id    timezone       language    country  something
---------------------------------------------------
0     Europe/London  en          45       x
1     Europe/Paris   fr          46

我已经查看了有关将数据透视表功能入侵到 MySQL 的 stackoverflow 的各种答案，类似但似乎没有一个答案与使用来自同一表的列的唯一行值的变量列名别名的情况相匹配.虽然我睡得很少，所以他们都开始变得有点模糊，提前道歉.

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.

我能找到的最接近的是使用准备好的语句 https://stackoverflow.com/a/986088/830171这将首先从 name 列中获取所有可能/唯一的值并构建一个使用 CASE WHEN 和/或多个子 SELECT 或 JOIN 的查询> 在同一个表查询中.

Closest I could find would be to use prepared statements https://stackoverflow.com/a/986088/830171 which would first get all possible/unique values from name column and build a query that uses CASE WHEN, and/or multiple sub-SELECT or JOIN on same table queries.

我能想到的替代方法是获取该用户 ID 的所有行并在应用程序本身的 for 循环中处理它们，或者尝试将名称限制为有限数量并使用 sub-SELECTs/JOINs.但是，如果添加了新名称，则第二个选项并不理想，我必须重新访问此查询.

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.

请告诉我我遗漏了一些明显的内容

Please tell me I've missed something obvious

推荐答案

与其他一些 RDBMS 不同，MySQL 没有对这种类型的透视操作设计的本机支持(开发人员认为它更适合到您的应用程序的表示层，而不是数据库层).

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).

如果你绝对必须在 MySQL 中执行这样的操作，构建一个准备好的语句是可行的方法—虽然我可能只使用 MySQL 的 GROUP_CONCAT() 函数:>

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:

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;

在 sqlfiddle 上查看.

注意GROUP_CONCAT() 受限于 group_concat_max_len 变量(默认为 1024 字节:除非您有一些非常长的 name 值，否则在这里不太可能相关).

Note that the result of GROUP_CONCAT() is limited by the group_concat_max_len variable (default of 1024 bytes: unlikely to be relevant here unless you have some extremely long name values).

这篇关于mysql选择动态行值作为列名，另一列作为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！