MySQLで同テーブルのレコード比較2

Question

https://teratail.com/questions/351061#reply-481003

上記で類似の質問をさせていただき解決へと導いていただいたのですが
少し違った内容の結果をもとめられ、ここ数日格闘してますが思うようにいきません。

|id|level|item|customer|
|:--|:--:|--:|
|1|1|商品A1|1|
|2|1|商品B1|2|
|3|1|商品C1|2|
|4|2|商品A2|1|
|5|2|商品B2|2|
|6|2|商品C2|1|
|7|3|商品A3|2|
|8|3|商品B3|1|
|9|3|商品C3|2|

上記のようなテーブルがあったとして
itemそれぞれの
「商品A1と商品A2と商品A3」,「商品B1と商品B2と商品B3」,「商品C1と商品C2と商品C3」をグループとして
levelが小さいレコードをcustomerごとに抽出するような
SQLは可能なのでしょうか？

customer 1の場合

|id|level|item|customer|
|:--|:--:|--:|
|1|1|商品A1|1|
|8|3|商品B3|1|
|6|2|商品C2|1|

や

cutomer 2の場合

|id|level|item|customer|
|:--|:--:|--:|
|7|3|商品A3|2|
|2|1|商品B1|2|
|3|1|商品C1|2|

Answer 1

まず、アイテムがどのグループに属するのかを決めるテーブルもしくはビューを作成します (ここでは例としてビューを用いました)。

CREATE VIEW GROUPING (item, grp) AS
            SELECT '商品A1', 'A'
  UNION ALL SELECT '商品A2', 'A'
  UNION ALL SELECT '商品A3', 'A'
  UNION ALL SELECT '商品B1', 'B'
  UNION ALL SELECT '商品B2', 'B'
  UNION ALL SELECT '商品B3', 'B'
  UNION ALL SELECT '商品C1', 'C'
  UNION ALL SELECT '商品C2', 'C'
  UNION ALL SELECT '商品C3', 'C'

あとは以下のようなSQLで目的を達成できると思います。

SELECT T1.id, T1.level, T1.item, T1.customer
FROM テーブル AS T1
JOIN GROUPING AS G1 ON T1.item = G1.item
WHERE NOT EXISTS (
  SELECT *
  FROM テーブル AS T2
  JOIN GROUPING AS G2 ON T2.item = G2.item
  WHERE T1.customer = T2.customer
  AND G1.grp = G2.grp
  AND T1.level > T2.level
)
AND T1.customer = 2

Comments

[takumil]

回答いただきありがとうございました。

Answer 2

SELECT *
FROM テーブル
WHERE (level, LEFT(item,3), customer) IN 
     (SELECT MIN(level), LEFT(item,3), customer
      FROM テーブル
      GROUP BY customer, LEFT(item,3)
     )
ORDER BY customer, item

http://sqlfiddle.com/#!9/bf35c4/10

Comments

[takumil]

回答いただきありがとうございました。

Answer 3

MySQLではなくSQL-Serverで検証してみました。

SELECT
   テーブル.id
 , テーブル.level
 , テーブル.item
 , テーブル.customer
FROM テーブル
INNER JOIN (
SELECT
   MIN(level) AS MINlevel
 , LEFT(item,3) AS ite
 , customer
FROM テーブル
WHERE customer = 1
GROUP BY 
   LEFT(item,3) 
 , customer
) AS A ON テーブル.level = A.MINlevel
     AND  LEFt(テーブル.item,3) = A.ite
     AND  テーブル.customer = A.customer
ORDER BY 
テーブル.item

提示頂いたテーブル内容だとうまくいくかも知れませんが
色々なデータが入ってきた場合意図した結果にならない可能性があります。
パズルとしては良いかも知れませんが
実運用にもっていくならテーブル設計を見直した方が良いと思いました。

Comments

[takumil]

回答いただきありがとうございました。