mysql - MySQL值作为标签矩阵

我在SQL Fiddle中的表中有以下数据。

http://sqlfiddle.com/#!9/6d8fd5/1

问题：在以下输出中，它在所有列中重复相同的值。

label           XS        S       M       L      XL      2XL     3XL     4XL
Chest girth     70       70      70      70      70      70      70      70
length          66       66      66      66      66      66      66      66
Sleeve length   62       62      62      62      62      62      62      62

解决方案：我想要XS列中的XS值，S列中的S值等。

我的SQL查询：

SELECT
  label,
  VALUE AS XS,
  VALUE AS S,
  VALUE AS M,
  VALUE AS L,
  VALUE AS XL,
  VALUE AS 2XL,
  VALUE AS 3XL,
  VALUE AS 4XL
FROM
  test
GROUP BY
   label

最佳答案

您可以使用如下查询来获得结果：

SELECT
  label,
  MAX(IF(size_label = 'XS', VALUE,null)) AS XS,
  MAX(IF(size_label = 'S ', VALUE,null)) AS S,
  MAX(IF(size_label = 'M',  VALUE,null)) AS M,
  MAX(IF(size_label = 'L',  VALUE,null)) AS L,
  MAX(IF(size_label = 'XL', VALUE,null)) AS XL,
  MAX(IF(size_label = '2XL',VALUE,null)) AS 2XL,
  MAX(IF(size_label = '3XL',VALUE,null)) AS 3XL,
  MAX(IF(size_label = '4XL',VALUE,null)) AS 4XL
FROM
  test
GROUP BY
   label;

样品

MariaDB [yourschema]> SELECT
    ->   label,
    ->   MAX(IF(size_label = 'XS', VALUE,null)) AS XS,
    ->   MAX(IF(size_label = 'S ', VALUE,null)) AS S,
    ->   MAX(IF(size_label = 'M',  VALUE,null)) AS M,
    ->   MAX(IF(size_label = 'L',  VALUE,null)) AS L,
    ->   MAX(IF(size_label = 'XL', VALUE,null)) AS XL,
    ->   MAX(IF(size_label = '2XL',VALUE,null)) AS 2XL,
    ->   MAX(IF(size_label = '3XL',VALUE,null)) AS 3XL,
    ->   MAX(IF(size_label = '4XL',VALUE,null)) AS 4XL
    -> FROM
    ->   test
    -> GROUP BY
    ->    label;
+---------------+------+------+------+------+------+------+------+------+
| label         | XS   | S    | M    | L    | XL   | 2XL  | 3XL  | 4XL  |
+---------------+------+------+------+------+------+------+------+------+
| Chest girth   |   70 |   71 |   72 |   73 |   74 |   75 |   76 |   77 |
| length        |   66 |   67 |   68 |   69 |   70 |   71 |   72 |   73 |
| Sleeve length |   62 |   63 |   64 |   65 |   66 |   67 |   68 |   69 |
+---------------+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

MariaDB [yourschema]>

关于mysql - MySQL值作为标签矩阵，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38671335/

xs

mysql - MySQL值作为标签矩阵