log

日記です

GROUP BY後、最小・最大の値を抽出する

地味に調べたのでメモ。

こたえ
SELECT
  m1.item_id,
  m1.price,
  m1.sale_price
FROM
  items m1,
  items m2
GROUP BY
  m1.item_id, m1.price, m1.sale_price, m2.category_id
HAVING
  MIN(m2.sale_price) = m1.sale_price;
サンプルテーブル作成
CREATE TABLE `items` (
  `category_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `sale_price` double DEFAULT NULL
);

INSERT INTO items VALUES(1, 1, 300, 150);
INSERT INTO items VALUES(1, 2, 200, 100);
INSERT INTO items VALUES(1, 3, 150, 110);
INSERT INTO items VALUES(2, 4, 1300, 1150);
INSERT INTO items VALUES(2, 5, 1200, 1100);
INSERT INTO items VALUES(2, 6, 1150, 1110);
サンプルテーブル確認
mysql> SELECT * FROM items;
+-------------+---------+-------+------------+
| category_id | item_id | price | sale_price |
+-------------+---------+-------+------------+
|           1 |       1 |   300 |        150 |
|           1 |       2 |   200 |        100 |
|           1 |       3 |   150 |        110 |
|           2 |       4 |  1300 |       1150 |
|           2 |       5 |  1200 |       1100 |
|           2 |       6 |  1150 |       1110 |
+-------------+---------+-------+------------+
6 rows in set (0.00 sec)
実行
mysql> SELECT
    ->   m1.item_id,
    ->   m1.price,
    ->   m1.sale_price
    -> FROM
    ->   items m1,
    ->   items m2
    -> GROUP BY
    ->   m1.item_id, m1.price, m1.sale_price, m2.category_id
    -> HAVING
    ->   MIN(m2.sale_price) = m1.sale_price;
+---------+-------+------------+
| item_id | price | sale_price |
+---------+-------+------------+
|       2 |   200 |        100 |
|       5 |  1200 |       1100 |
+---------+-------+------------+
2 rows in set (0.00 sec)

追記

うーん、、、なんかデータが多いと微妙な気がしたのでプログラム側で集計するようにしました。MySQLにもRANK関数があればいいんですけどねぇ