mysql - 选择一列的最小值另一列的最大值

Question

我有如下数据:

PID SID CID Price
1   1   1   18
2   1   1   19
3   1   2   10
4   2   2   22
5   2   2   21.35
6   3   2   25
7   3   7   30
8   3   2   40

我想为每个 SID 选择 CID 最小且价格最大的行。

预期输出:

PID SID CID Price
2   1   1   19
4   2   2   22
8   3   2   40

我的查询给出了预期的输出。但由于我是 MySQL 的新手，我不确定这是否是最佳的方式。

查询:

select a.PID, a.SID, a.CID, a.Price
from Products a
inner join (select SID as SID, min(CID) as CID_Min, max(Price) as Price_Max
            from Products
            group by SID) b
on a.SID=b.SID and
   a.CID=b.CID_Min and
   a.Price=b.Price_Max;

编辑 #1:抱歉，我观察到如下数据集，查询不返回任何输出:

PID SID CID Price
11  6   1   18
12  6   1   19
13  6   2   30

但是预期的输出是:

PID SID CID Price
11  6   1   19

由于 SID=6 的最小 CID 为 1。并且根据值 SID=6 和 CID=1，Price 的最大值为 19。

知道如何实现它。

这个查询是否最优:

select t.SID, t.CID, t.Price
from Products t
inner join
  (select p.SID as SID_max, p.CID as CID_max, max(p.Price) as Price_max
   from Products p
   inner join 
     (select SID as SID_min, min(CID) as CID_min
      from Products
      group by SID) p_min
   on p.SID=p_min.SID_min and
      p.CID=p_min.CID_min
   group by p.SID, p.CID
  ) p_max
on t.SID=p_max.SID_max and
   t.CID=p_max.CID_max and
   t.Price=p_max.Price_max

Answer 1

当然你想要 12, 6, 1, 19 !?!?!?!?!?!?!

SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT x.sid
            , x.cid
            , MAX(price) price 
         FROM my_table x 
         JOIN 
            ( SELECT sid
                   , MIN(cid) cid 
                FROM my_table 
               GROUP 
                  BY sid
            ) y 
           ON y.sid = x.sid 
          AND y.cid = x.cid 
        GROUP 
           BY sid
            , cid
     ) b 
    ON b.sid = a.sid 
   AND b.cid = a.cid 
   AND b.price = a.price;
+-----+-----+-----+-------+
| pid | sid | cid | price |
+-----+-----+-----+-------+
|  12 |   6 |   1 |    19 |
+-----+-----+-----+-------+