ここに以下のようなデータがあります。
mysql> select * from item;
+----+-----------+--------+---------------+------------+
| id | item_code | color | is_main_color | is_deleted |
+----+-----------+--------+---------------+------------+
| 1 | 100000 | BLACK | 0 | 0 |
| 2 | 100000 | BLUE | 0 | 0 |
| 3 | 100000 | RED | 0 | 1 |
| 4 | 100000 | WHITE | 1 | 0 |
| 5 | 200000 | YELLOW | 0 | 0 |
| 6 | 200000 | GREEN | 0 | 0 |
| 7 | 200000 | PINK | 0 | 0 |
| 8 | 300000 | BLACK | 0 | 1 |
| 9 | 300000 | BLUE | 0 | 0 |
| 10 | 300000 | YELLOW | 0 | 0 |
+----+-----------+--------+---------------+------------+
ここから下記の条件でデータを取得する必要があります。
データ取得条件
1. 削除済み（is_deleted=1）は除外
2. item_code でグルーピングして
3. メインカラー（is_main_color=1）のカラーを取得
4. ただしメインカラーフラグが立っていない item については color で ソートした1件目を取得する
MySQLでこういったグルーピングした中の最初の1件を取得するのって結構難しいですが、ユーザー定義変数を使ってこれを実現できます。
結論
先に結論を書くと以下のようになります。
SELECT * FROM (
SELECT
@seq:=IF(@previous=t1.item_code, @seq+1, 1) AS seq,
@previous:=t1.item_code as previous,
t1.*
FROM
(
SELECT *
FROM item
WHERE is_deleted = 0
ORDER BY item_code, is_main_color DESC, color
) as t1,
(SELECT @seq:=1, @previous:=NULL) t2
) AS t3
WHERE t3.seq = 1;
ユーザー定義変数を初めて見る人は混乱しそうですね。
ユーザー定義変数とは
あるステートメント内のユーザー定義変数に値を格納し、あとから別のステートメントでこれを参照できます。
具体的には
mysql> set @A = 1;
上記のようにセットすると
mysql> select @A;
+------+
| @A |
+------+
| 1 |
+------+
このように後から参照できます。
割り当て演算子は = の代わりに := でセットすることもできます。
mysql> set @B := 100;
mysql> select @B;
+------+
| @B |
+------+
| 100 |
+------+
2種類あるのは SET 以外のステートメントで利用する場合は = が比較演算子となるためです。
例えばSELECT文の中で使用する場合は以下のようになります。
SELECT @C:=@A+@B FROM XXXXXX WHERE XXX = XXX;
SQLの作成
それではこれを使ってSQLを組み立てていきます。
不要なデータの除外
まずは不要なデータを除外しソートします。
削除済みのデータを除外し、メインカラーフラグ（1 -> 0）、カラーの順にソートします。
(
SELECT *
FROM item
WHERE is_deleted = 0
ORDER BY item_code, is_main_color DESC, color
) as t1,
ユーザー定義変数を利用して順番付け
@seq は item_code でグルーピングしてソートした中の順番を
@previous は比較用に1つ前のレコードの item_code をセットしています。
SET @seq=1;
SET @previous=NULL;
SELECT
@seq:=IF(@previous=t1.item_code, @seq+1, 1) AS seq,
@previous:=t1.item_code AS previous,
t1.*
FROM
(
SELECT *
FROM item
WHERE is_deleted = 0
ORDER BY item_code, is_main_color DESC, color
) AS t1;
結果は以下のようになります。
seq が item_code 単位の連番になっています。
+------+----------+----+-----------+--------+---------------+------------+
| seq | previous | id | item_code | color | is_main_color | is_deleted |
+------+----------+----+-----------+--------+---------------+------------+
| 1 | 100000 | 4 | 100000 | WHITE | 1 | 0 |
| 2 | 100000 | 1 | 100000 | BLACK | 0 | 0 |
| 3 | 100000 | 2 | 100000 | BLUE | 0 | 0 |
| 1 | 200000 | 6 | 200000 | GREEN | 0 | 0 |
| 2 | 200000 | 7 | 200000 | PINK | 0 | 0 |
| 3 | 200000 | 5 | 200000 | YELLOW | 0 | 0 |
| 1 | 300000 | 9 | 300000 | BLUE | 0 | 0 |
| 2 | 300000 | 10 | 300000 | YELLOW | 0 | 0 |
+------+----------+----+-----------+--------+---------------+------------+
SQLを上から順に見ていくと
SET @seq=1;
SET @previous=NULL;
ここでユーザー定義変数をセットしています。
@seq:=IF(@previous=t1.item_code, @seq+1, 1) AS seq,
ここで一つ前の item_code と比較して、変わっていなければ @seq をインクリメントし、変わっていたら1に戻しています。
@previous:=t1.item_code AS previous,
ここでは次のレコードで比較に使うために、現在の item_code を @previous にセットしています。
1番目のレコードだけに絞る
あとは seq=1 のデータだけを取得すれば「グルーピングしてソートした最初の1件」を取得可能ですが、WHERE句ではカラムエイリアスへの参照は許可されていないため、以下のようにします。
SET @seq=1;
SET @previous=NULL;
SELECT * FROM (
SELECT
@seq:=IF(@previous=t1.item_code, @seq+1, 1) AS seq,
@previous:=t1.item_code as previous,
t1.*
FROM
(
SELECT *
FROM item
WHERE is_deleted = 0
ORDER BY item_code, is_main_color DESC, color
) as t1
) AS t2
WHERE t2.seq = 1;
おまけ
以上で完成なのですが、FROM句に以下のよう書いてSET句を省略してしまうことも可能です。
(SELECT @seq:=1, @previous:=NULL) t2
これが可能なのは SELECT 文の処理順序によるもの（SELECT句よりもFROM句が先に処理される）と思われますが、MySQLのドキュメントでは処理順序に関する記述を見つけられませんでした。
誰か知っていたら教えてください。
結果
最後に実行結果を見てみましょう。
mysql> SELECT * FROM (
-> SELECT
-> @seq:=IF(@previous=t1.item_code, @seq+1, 1) AS seq,
-> @previous:=t1.item_code as previous,
-> t1.*
-> FROM
-> (
-> SELECT *
-> FROM item
-> WHERE is_deleted = 0
-> ORDER BY item_code, is_main_color DESC, color
-> ) as t1,
-> (SELECT @seq:=1, @previous:=NULL) t2
-> ) AS t3
-> WHERE t3.seq = 1;
+------+----------+----+-----------+-------+---------------+------------+
| seq | previous | id | item_code | color | is_main_color | is_deleted |
+------+----------+----+-----------+-------+---------------+------------+
| 1 | 100000 | 4 | 100000 | WHITE | 1 | 0 |
| 1 | 200000 | 6 | 200000 | GREEN | 0 | 0 |
| 1 | 300000 | 9 | 300000 | BLUE | 0 | 0 |
+------+----------+----+-----------+-------+---------------+------------+
期待通りの結果になっていますね。