在mysql中选择动态列 [英] Select dynamic Columns in mysql

问题描述

是否可以遍历这样的表:

Is it possible to traverse a table like this:

mysql> select * from `stackoverflow`.`Results`;
+--------------+---------+-------------+--------+
| ID           | TYPE    | CRITERIA_ID | RESULT |
+--------------+---------+-------------+--------+
|            1 | car     | env         | 1      |
|            2 | car     | gas         |        |
|            3 | car     | age         |        |
|            4 | bike    | env         | 1      |
|            5 | bike    | gas         |        |
|            6 | bike    | age         | 1      |
|            7 | bus     | env         | 1      |
|            8 | bus     | gas         | 1      |
|            9 | bus     | age         | 1      |
+--------------+---------+-------------+--------+
9 rows in set (0.00 sec)

对此:

+------+-----+-----+-----+
| TYPE | env | gas | age |
+------+-----+-----+-----+
| car  | 1   |     |     |
| bike | 1   |     | 1   |
| bus  | 1   | 1   | 1   |
+------+-----+-----+-----+

目标是选择所有CRITERIA_ID并将其用作列. 作为行，我喜欢使用所有TYPE.

The aim is to select all the CRITERIA_IDs and use them as a column. As rows i like to use all the TYPEs .

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

• All Criterias: SELECT distinct(CRITERIA_ID) FROM stackoverflow.Results;
• All Types SELECT distinct(TYPE) FROM stackoverflow.Results;

但是如何将它们组合成一个视图或某物.这样吗?

But how combine them into a view or smth. like this?

如果您喜欢处理数据.这是生成表的脚本:

If you like to play with the data. This is a script to generate the table:

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 );

推荐答案

不幸的是，MySQL没有PIVOT函数，这基本上就是您要尝试的功能.因此，您将需要在CASE语句中使用聚合函数:

Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement:

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小提琴

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

Now if you want to perform this dynamically, meaning you do not know ahead of time the columns to transpose, then you should review the following article:

动态数据透视表(将行转换为列)

您的代码如下:

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提琴

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