在MySQL中，如何动态地选择一个表的值作为另一个表的列?

时间：2021-07-13 07:25:45

I'm no MySQL guru, but I get around the basic stuff pretty well. Thanks for your feedback.

我不是MySQL大师，但我能很好地解决基本问题。谢谢你的反馈。

I have two tables user and favorite. Each user can have multiple unique favorites.

我有两个表用户和最爱。每个用户可以有多个唯一的收藏夹。

table user u

表用户u

[ user_id + name  ]
  100     | Sally

table favorite fav

表最喜欢的最喜欢

[ fav_id  + user_id +  fav_key  +  fav_val ]
  1       | 100     | icecream  |    mint
  2       | 100     | candybar  |  snickers
  3       | 100     | color     |    red

I want to create a SELECT statement that will turn the user's favorites into columns using the fav_key value as the column header. *The problem is I will never know what the fav_val value will be as these are user entered, so the column names have to be generated dynamically.

我想创建一个SELECT语句，使用fav_key值作为列标题将用户的收藏项转换为列。*问题是，当用户输入这些值时，我永远不会知道fav_val值是多少，因此必须动态生成列名。

SELECT ...

选择……

[ user_id + name  + fav_icecream + fav_candybar + fav_color ]
  100     | Sally |    mint      |   snickers   |   red

With some distant thought of performance in mind -- one of the issues is that I don't want to run two SELECT statements to get the user data and the user favorites (plus I like the idea of having the columns dynamically named in this way).

考虑到性能的一些遥远的想法——其中一个问题是，我不想运行两个SELECT语句来获取用户数据和用户最爱(另外，我喜欢以这种方式动态命名列)。

UPDATE

更新

So this is called, pivoting, excellent.

这叫做，旋转，很好。

What if I don't ever know what the values are? I need to dynamically generate the columns from a query on favorites.

如果我不知道这些值是什么呢?我需要从favorites的查询中动态地生成列。

2 个解决方案

#1

4  

Like this:

是这样的:

Select fav.user_id, u.name 
       MAX(case WHEN fav.fav_key = 'icecream' then fav.fav_val end) as 'fav_icecream',
       MAX(case WHEN fav.fav_key = 'candybar'  then fav.fav_val end) as 'fav_candybar',
       MAX(case WHEN fav.fav_key = 'color'    then fav.fav_val end) as 'fav_color'
From favorite fav
     inner join users u on fav.user_id = u.user_id
group by fav.user_id

Working Example: DEMO

例如:工作演示

Note that: the demo is for MS SQL Server but it is working the same way for mysql, I just tried to give you a live demo.

请注意:演示是针对MS SQL Server的，但它对mysql的工作方式是一样的，我只是尝试给您一个现场演示。

#2

3  

Essentially you want PIVOT functionality. Unfortunately, that's not natively supported by MySQL (unlike Oracle or SQL Server). There are many questions on * showing how to work around that lacking functionality of MySQL:

本质上你想要的是主功能。不幸的是，MySQL并不支持这一点(不像Oracle或SQL Server)。关于*有很多问题，告诉我们如何解决MySQL缺乏功能的问题:

https://*.com/search?q=mysql+pivot+sql

https://*.com/search?q=mysql +主+ sql

Some examples:

一些例子:

#1

4  

Like this:

是这样的:

Select fav.user_id, u.name 
       MAX(case WHEN fav.fav_key = 'icecream' then fav.fav_val end) as 'fav_icecream',
       MAX(case WHEN fav.fav_key = 'candybar'  then fav.fav_val end) as 'fav_candybar',
       MAX(case WHEN fav.fav_key = 'color'    then fav.fav_val end) as 'fav_color'
From favorite fav
     inner join users u on fav.user_id = u.user_id
group by fav.user_id

Working Example: DEMO

例如:工作演示

Note that: the demo is for MS SQL Server but it is working the same way for mysql, I just tried to give you a live demo.

请注意:演示是针对MS SQL Server的，但它对mysql的工作方式是一样的，我只是尝试给您一个现场演示。

#2

3  

Essentially you want PIVOT functionality. Unfortunately, that's not natively supported by MySQL (unlike Oracle or SQL Server). There are many questions on * showing how to work around that lacking functionality of MySQL:

本质上你想要的是主功能。不幸的是，MySQL并不支持这一点(不像Oracle或SQL Server)。关于*有很多问题，告诉我们如何解决MySQL缺乏功能的问题:

https://*.com/search?q=mysql+pivot+sql

https://*.com/search?q=mysql +主+ sql

Some examples:

一些例子:

标签：

相关文章