mysql动态选取列_在mysql中选择动态列

是否可以遍历这样的表：

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.

>所有标准：SELECT distinct(CRITERIA_ID)FROM stackoverflow.Results;

>所有类型SELECT distinct(TYPE)FROM stackoverflow.Results;

但是如何将它们组合成视图或smth.像这样？

如果你喜欢玩数据.这是一个生成表的脚本：

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