More than 5 years have passed since last update.

MySQL -- 複数レコードを、横に並べる例

Last updated at 2017-05-23Posted at 2017-05-23

1_準備するもの

CREATE TABLE `test`.`userProps` (
  `idUser` VARCHAR(16) NOT NULL COMMENT '',
  `seqProp` INT NOT NULL COMMENT '',
  `nameProp` VARCHAR(45) NOT NULL COMMENT '');

INSERT INTO `test`.`userProps` (`idUser`, `seqProp`, `nameProp`) VALUES ('userABC', '1', '心');
INSERT INTO `test`.`userProps` (`idUser`, `seqProp`, `nameProp`) VALUES ('userABC', '2', '技');
INSERT INTO `test`.`userProps` (`idUser`, `seqProp`, `nameProp`) VALUES ('userABC', '3', '体');

2_実際のSQL

SELECT idUser, GROUP_CONCAT(seqProp ORDER BY FIELD (seqProp, 1, 2, 3)) AS tempCSV FROM userProps GROUP BY idUser HAVING tempCSV='1,2,3';

3_SQL結果

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up