本文介绍了mysql选择动态行值作为列名,另一列作为值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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 它将首先从名称列中获取所有可能的/唯一的值,并建立一个在同一表查询上使用CASE WHEN和/或多个sub- SELECTJOIN的查询.

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- SELECT /JOIN s.但是,如果添加了新名称,那么第二个选项并不理想,我必须重新访问该查询.

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中执行这样的操作,则构建预备语句是解决问题的方法-尽管不要弄乱CASE,我可能只会使用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选择动态行值作为列名,另一列作为值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 12:46
查看更多