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

发布于 2025-01-05 09:41:06 字数 1009 浏览 1 评论 0原文

我不是 MySQL 专家，但我很好地解决了基本问题。感谢您的反馈。

我有两个表user 和favorite。每个用户可以有多个独特的收藏夹。

table user 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

我想创建一个 SELECT 语句，该语句将使用 fav_keyfav_key 将用户的收藏夹转换为列code> value 作为列标题。 *问题是我永远不会知道 fav_val 值是什么，因为这些值是用户输入的，因此必须动态生成列名称。

SELECT ...

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

考虑到性能方面的一些遥远的想法 - 问题之一是我不想运行两个 SELECT 语句来获取用户数据和用户收藏夹（另外我喜欢以这种方式动态命名列的想法）。

更新

所以这就是所谓的，旋转，优秀。

如果我不知道这些值是什么怎么办？我需要通过对收藏夹的查询动态生成列。

原文

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

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

table user 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 ...

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

同尘 2025-01-12 09:41:06

像这样：

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

工作示例：DEMO

注意该演示适用于 MS SQL Server，但它对于 mysql 的工作方式相同，我只是想给您一个现场演示。

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.

回复收藏 0 原文