在mysql中选择动态列

一尘不染

在mysql中选择动态列

mysql

是否可以遍历这样的表：

mysql> select * from`stackoverflow`.`结果`;
+ -------------- + --------- + ------------- + -------- +
| ID | TYPE | CRITERIA_ID | 结果
+ -------------- + --------- + ------------- + -------- +
| 1 | 汽车| env | 1 |
| 2 | 汽车| 煤气| |
| 3 | 汽车| 年龄| |
| 4 | 自行车| env | 1 |
| 5 | 自行车| 煤气| |
| 6 | 自行车| 年龄| 1 |
| 7 | 巴士| env | 1 |
| 8 | 巴士| 煤气| 1 |
| 9 | 巴士| 年龄| 1 |
+ -------------- + --------- + ------------- + -------- +
设置9行（0.00秒）

变成这个：

+ ------ + ----- + ----- + ----- +
| TYPE | env | 煤气| 年龄|
+ ------ + ----- + ----- + ----- +
| 汽车| 1 | | |
| 自行车| 1 | | 1 |
| 巴士| 1 | 1 | 1 |
+ ------ + ----- + ----- + ----- +

目的是选择所有CRITERIA_IDs并将它们用作列。作为行，我喜欢使用所有TYPEs。

所有条件： SELECT distinct(CRITERIA_ID) FROM stackoverflow.Results;
所有类型 SELECT distinct(TYPE) FROM stackoverflow.Results;

但是如何将它们组合成一个视图或某物。像这样？

如果您喜欢玩数据。这是生成表的脚本：

CREATE SCHEMA `stackoverflow`;
CREATE TABLE `stackoverflow`.`Results` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `TYPE` varchar(50) NOT NULL,
  `CRITERIA_ID` varchar(5) NOT NULL,
  `RESULT` bit(1) NOT NULL,
  PRIMARY KEY (`ID`)
) 
ENGINE=InnoDB;

INSERT INTO `stackoverflow`.`Results`
(
 `ID`,
 `TYPE`,
 `CRITERIA_ID`,
 `RESULT`
)
VALUES
( 1, "car", env, true ),
( 2, "car", gas, false ),
( 3, "car", age, false ),
( 4, "bike", env, true ),
( 5, "bike", gas, false ),
( 6, "bike", age, true ),
( 7, "bus", env, true ),
( 8, "bus", gas, true ),
( 9, "bus", age, true );

阅读 263

2020-05-17

共1个答案

一尘不染

不幸的是，MySQL没有PIVOT您基本上想做的功能。因此，您将需要在CASE语句中使用聚合函数：

SELECT type,
  sum(case when criteria_id = 'env' then result end) env,
  sum(case when criteria_id = 'gas' then result end) gas,
  sum(case when criteria_id = 'age' then result end) age
FROM results
group by type

参见带有演示的SQL Fiddle

现在，如果您要动态执行此操作，这意味着您不知道要转置的列的提前时间，那么您应该查看以下文章：

动态数据透视表（将行转换为列）

您的代码如下所示：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(CRITERIA_ID = ''',
      CRITERIA_ID,
      ''', RESULT, NULL)) AS ',
      CRITERIA_ID
    )
  ) INTO @sql
FROM
  Results;
SET @sql = CONCAT('SELECT type, ', @sql, ' FROM Results GROUP BY type');

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

参见带有演示的SQL Fiddle

2020-05-17