分组求极值

分组按指定字段排序求极值

数据截图

需求

按p_id分组后，获取min(p_order)对应行a的值，max(p_order)对应行b的最大值

sql

第一种方式：

SELECT p_id,b,a
from
(
SELECT temp3.*, temp4.b FROM
(
SELECT test_xw.p_id, a FROM test_xw
inner join
(SELECT min(id) as id from test_xw GROUP BY p_id ORDER BY p_order) temp1
on test_xw.id = temp1.id
) temp3
inner join 
(
SELECT test_xw.p_id, b FROM test_xw
inner join
(SELECT max(id) as id from test_xw GROUP BY p_id ORDER BY p_order) temp2
on test_xw.id = temp2.id
)temp4
on temp3.p_id = temp4.p_id
) temp

第二种方式：

SELECT
 p_id,
 SUBSTRING_INDEX(temp.str, ',' ,- 1) , SUBSTRING_INDEX(temp.str, ',', 1)
FROM
 (
  SELECT
   p_id,
   GROUP_CONCAT(
    CONCAT(a, ',', b)
    ORDER BY
     p_order,
     ','
   ) str
  FROM
   test_xw
  GROUP BY
   p_id
 ) temp;

备注：sql不是太熟练，如果能给出更好的解决办法欢迎留言