mysql正交表转

Question

我有如下架构:

CREATE TABLE prop_set (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      name varchar(255),
      PRIMARY KEY (id)
  );
CREATE TABLE users_props (
      user_id int UNSIGNED NOT NULL,
      prop_id int UNSIGNED NOT NULL,
      value text, 
      PRIMARY KEY (user_id,prop_id)
  );
CREATE TABLE user (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      name varchar(255),
      PRIMARY KEY (id)
  );

INSERT INTO prop_set SET name="prop1";
INSERT INTO prop_set SET name="prop2";
INSERT INTO prop_set SET name="prop3";


INSERT INTO user SET name="user1";
INSERT INTO user SET name="user2";

INSERT INTO users_props set user_id=1,prop_id=1,value="prop1 user1";
INSERT INTO users_props set user_id=1,prop_id=2,value="prop2 user1";
INSERT INTO users_props set user_id=1,prop_id=3,value="prop3 user1";

INSERT INTO users_props set user_id=2,prop_id=1,value="prop1 user2";
INSERT INTO users_props set user_id=2,prop_id=2,value="prop2 user2";
INSERT INTO users_props set user_id=2,prop_id=3,value="prop3 user2";

现在我按如下方式运行选择:

SELECT u.name,ps.name AS prop,up.value
      FROM USER u
        JOIN users_props up ON u.id=up.user_id
        JOIN prop_set ps ON ps.id=up.prop_id;

并得到输出:

|  NAME |  PROP |       VALUE |
-------------------------------
| user1 | prop1 | prop1 user1 |
| user2 | prop1 | prop1 user2 |
| user1 | prop2 | prop2 user1 |
| user2 | prop2 | prop2 user2 |
| user1 | prop3 | prop3 user1 |
| user2 | prop3 | prop3 user2 |

fiddle有没有办法让mysql返回如下输出？

| NAME  |   prop1     |     prop2   |    prop3    |
---------------------------------------------------
| user1 | prop1 user1 | prop2 user1 | prop3 user1 |
| user2 | prop1 user2 | prop2 user2 | prop3 user2 |

我正在寻找 mysql - 唯一的解决方案。

Answer 1

MySQL 的pivot 语法使用聚合函数和类似这样的CASE 语句:

SELECT u.name,
  max(case when ps.name = 'prop1' then up.value else null end) Prop1,
  max(case when ps.name = 'prop2' then up.value else null end) Prop2,
  max(case when ps.name = 'prop3' then up.value else null end) Prop3
FROM user u 
JOIN users_props up 
  ON u.id=up.user_id 
JOIN prop_set ps 
  on ps.id=up.prop_id
GROUP BY u.name;

参见 SQL Fiddle with Demo

如果您知道要转置到列中的值，上面的方法非常有用，但如果这些值未知，那么您将希望使用 prepared statement 来查看生成动态 SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when ps.name = ''',
      name,
      ''' then up.value else null end) AS ''',
      name, ''''
    )
  ) INTO @sql
FROM  prop_set;

SET @sql = CONCAT('SELECT u.name, ', @sql, ' 
                  FROM user u 
                  JOIN users_props up 
                    ON u.id=up.user_id 
                  JOIN prop_set ps 
                    on ps.id=up.prop_id
                  GROUP BY u.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参见 SQL Fiddle with Demo

两个版本的结果相同:

|  NAME |       PROP1 |       PROP2 |       PROP3 |
---------------------------------------------------
| user1 | prop1 user1 | prop2 user1 | prop3 user1 |
| user2 | prop1 user2 | prop2 user2 | prop3 user2 |