是否有可能像这样遍历一个表:
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_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 );