サンプルDBの作成

DROP SCHEMA IF EXISTS `category_test` ;

CREATE SCHEMA IF NOT EXISTS `category_test` DEFAULT CHARACTER SET utf8 ;

DROP TABLE IF EXISTS `category_test`.`item` ;

CREATE TABLE IF NOT EXISTS `category_test`.`item` (
    `item_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `item_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`item_id`,`item_name`)
   )
ENGINE = InnoDB;


DROP TABLE IF EXISTS `category_test`.`category` ;

CREATE TABLE IF NOT EXISTS `category_test`.`category` (
    `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`category_id`),
    UNIQUE INDEX `uq_category_name` (`category_name` ASC)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS `category_test`.`item_category` ;

CREATE TABLE IF NOT EXISTS `category_test`.`item_category` (
    `item_id` INT UNSIGNED NOT NULL,
    `category_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`item_id`, `category_id`),
    CONSTRAINT `fk_item_id`
        FOREIGN KEY (`item_id`)
        REFERENCES `category_test`.`item` (`item_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT `fk_category_id`
        FOREIGN KEY (`category_id`)
        REFERENCES `category_test`.`category` (`category_id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE)
ENGINE = InnoDB;
SET sql_mode = '';
USE `category_test`

テストデータ

INSERT INTO `item` VALUES(1,'Mac book'),(2,'Mac book Air'),(3,'Mac book Pro'),(4,'iMac'),(5,'Mac mini'),(6,'Mac Pro');
INSERT INTO `category` VALUES(1,'Large display'),(2,'Small display'),(3,'No display'),(4,'Desktop'),(5,'Laptop');
INSERT INTO `item_category` VALUES(1,2),(1,5),(2,2),(2,5),(3,1),(3,5),(4,1),(4,4),(5,3),(5,4),(6,3),(6,4);

まずはやり方を考える

とりあえず、複数のSQLになってもいいので商品とそれに紐付けされた複数のカテゴリ情報を取得する方法を考えます。

商品情報だけを取得した後、その商品のidからカテゴリを取得する
商品情報の部分が重複してもいいので、第一正規形にして取得する
設定できるカテゴリ数を制限し、非正規形で取得する
設定したカテゴリをCSVなりTSVで取得する

普通にやるとしたら1か2ですが、今回の目的が一つのSQLなので1は使えません。また、2の場合はページネーションへの対応やカテゴリの絞り込みの時に、余分なデータを取得したり正しいデータを表示するのが困難な場合があります。そして、3の場合は自由度が下がるのと同時に大量の空データが出てくるのでオススメできません。となると今回選ぶべきは4となるので、JOINした値をCSVでまとめる方法を探します。

グループをCSVで取得する

GROUP_CONCAT

GROUP_CONCATはMySQLで利用できる機能の一つで、GROUP BYでグループ化されたデータを指定した文字列で結合したりする時に使います詳しくは公式で調べてみてください。

GROUP_CONCATでCSV出力をしてみる

以下のSQLを発行します

SELECT `item_id`,`item_name`,GROUP_CONCAT(`category_id` SEPARATOR ',') AS `category_id`,GROUP_CONCAT(`category_name` SEPARATOR ',') AS `category_name`
FROM `item`
JOIN `item_category`
USING (`item_id`)
JOIN `category`
USING (`category_id`)
GROUP BY (`item_id`);

するとこんな感じで結果が返ってきます

item_id	item_name	category_id	category_name
1	Mac book	2,5	Small display,Laptop
2	Mac book Air	2,5	Small display,Laptop
3	Mac book Pro	5,1	Laptop,Large display
4	iMac	4,1	Desktop,Large display
5	Mac mini	4,3	Desktop,No display
6	Mac Pro	5,4	Desktop,No display

データにカンマがある場合は他の値で区切って下さい

CSV内の絞りこみに対応する

GROUP_CONCAT内での並べ替えはGROUP_CONCAT(カラム名 ORDER BY 条件)になります。

SELECT `item_id`,`item_name`,GROUP_CONCAT(`category_id` ORDER BY `category_id` SEPARATOR ',') AS `category_id`,GROUP_CONCAT(`category_name` ORDER BY `category_id` SEPARATOR ',') AS `category_name`
FROM `item`
JOIN `item_category`
USING (`item_id`)
JOIN `category`
USING (`category_id`)
GROUP BY (`item_id`);

実行結果

item_id	item_name	category_id	category_name
1	Mac book	2,5	Small display,Laptop
2	Mac book Air	2,5	Small display,Laptop
3	Mac book Pro	1,5	Large display,Laptop
4	iMac	1,4	Large display,Desktop
5	Mac mini	3,4	No display,Desktop
6	Mac Pro	3,4	No display,Desktop

今回のように複数のCSVを作る時には並べ替えの条件を合わせることをわすれないようにしてください

CSV内の値を使った絞りこみ

CSV内の値を使った絞りこみはFIND_IN_SETを使います。

SELECT `item_id`,`item_name`,GROUP_CONCAT(`category_id` ORDER BY `category_id` SEPARATOR ',') AS `category_id`,GROUP_CONCAT(`category_name` ORDER BY `category_id` SEPARATOR ',') AS `category_name`
FROM `item`
JOIN `item_category`
USING (`item_id`)
JOIN `category`
USING (`category_id`)
GROUP BY (`item_id`)
HAVING FIND_IN_SET(5,`category_id`);