MYSQL，动态行到列 [英] MYSQL, dynamic rows to columns

问题描述

我有一个表用户，看起来像这样(涉及更多列)

I have a table user that looks something like this (more columns involved)

user_id | user_name |
1       |   adam    |
2       |   berta   |
3       |   caesar  |

看起来像这样的餐桌产品

a table product that looks something like this

product_id | product_name |
1          |   product 1  |
2          |   product 2  |
3          |   product 3  |
4          |   product 4  |

和一个表u_p，该表引用允许用户使用的产品

and a table u_p that refers to products a user is allowed to use

u_p_id  | u_p_user  | u_p_prod  |
1       |   1       |   1       |
2       |   1       |   2       |
3       |   2       |   3       |

其中u_p_user和u_p_prod是user_id分别的外键. product_id.

where u_p_user and u_p_prod are the foreign keys to user_id resp. product_id.

这是我想要的:

user_id | user_name | u_p_prod=1 | u_p_prod=2 | u_p_prod=3 | u_p_prod=4 |
1       |   adam    | 1          | 1          | 0          | 0          |
2       |   berta   | 0          | 0          | 1          | 0          |
3       |   caesar  | 0          | 0          | 0          | 0          |

用户和产品必须是动态的，当然.

users and products have to be dynamic, off course.

如何在mySQL中完成此操作?

How can this be done in mySQL?

推荐答案

这通常称为枢轴.如果不对每个不同的值硬编码一列，就无法进行透视.这意味着您需要在编写SQL查询之前 知道一组不同的值.

This is often called a pivot. There's no way to do a pivot without hard-coding one column per distinct value. This means you need to know the set of distinct values before you write the SQL query.

SELECT u.user_id, u.user_name,
  COALESCE(MAX(IF(u_p.prod=1, 1, 0), 0) AS `u_p_prod=1`,
  COALESCE(MAX(IF(u_p.prod=2, 1, 0), 0) AS `u_p_prod=2`,
  COALESCE(MAX(IF(u_p.prod=3, 1, 0), 0) AS `u_p_prod=3`,
  COALESCE(MAX(IF(u_p.prod=4, 1, 0), 0) AS `u_p_prod=4`
FROM user AS u
LEFT OUTER JOIN u_p ON u.user_id = u_p.u_p_user
GROUP BY u.user_id;

这篇关于MYSQL，动态行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！