mysql - MySQL水平连接，表值作为列

我有3个表格项目，item_sizes，具有以下字段的大小：

items
id
name

item_sizes
item_id
size_id

sizes
id
size

尺寸具有以下值：S，M，XL，XXL等
我想要加入或以任何方式获得此结果：

item_id   |   name   |   S   |   M   |  XL  ...
--------------------------------------------
1             shirt      1       0      1
2             dress      0       1      1
3             jacket     1       1      1

等等。值1表示此项目具有此大小。
是否有可能做到这一点？

最佳答案

SELECT  a.id,
        a.name,
        SUM(CASE WHEN c.size = 'S' THEN 1 ELSE 0 END) `S`,
        SUM(CASE WHEN c.size = 'M' THEN 1 ELSE 0 END) `M`,
        SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) `L`,
        SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) `XL`,
        SUM(CASE WHEN c.size = 'XXL' THEN 1 ELSE 0 END) `XXL`
FROM    items a
        LEFT JOIN item_sizes b
            ON a.id = b.item_id
        LEFT JOIN sizes c
            ON b.size_ID = c.id
GROUP   BY a.id, a.name

使用PreparedStatament

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN c.size = ''',
      size,
      ''' then 1 ELSE 0 end) AS ',
      size
    )
  ) INTO @sql
FROM sizes;

SET @sql = CONCAT('SELECT  a.id,
                           a.name, ', @sql, '
                  FROM    items a
                          LEFT JOIN item_sizes b
                              ON a.id = b.item_id
                          LEFT JOIN sizes c
                              ON b.size_ID = c.id
                  GROUP   BY a.id, a.name');

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

关于mysql - MySQL水平连接，表值作为列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14642986/