mysql - 如何分别查找每个组的 MAX Count 值

Question

这是我的查询

SELECT q2.item_code
     , q2.SELL_DELIVERY_AMOUNT
     , q2.count_amount
  FROM 
     ( SELECT i.item_code
            , s.SELL_DELIVERY_AMOUNT 
            , count(s.SELL_DELIVERY_AMOUNT) count_amount
         FROM sell_delivery_items s 
            , items i
        WHERE s.item_id = i.item_id
        GROUP 
           BY SELL_DELIVERY_AMOUNT
            , item_code
     ) q2
 order 
    by item_code
     , count_amount desc 

结果是

item_code   SELL_DELIVERY_AMOUNT     count_amount
0001        50                       2
0001        100                      1
0002        150                      3

我需要仅显示每个 item_code 组的最高 count_amount 行。我已经搜索了一些答案，但仍然无法应用到我的答案。

这就是我想要的结果

item_code   SELL_DELIVERY_AMOUNT     count_amount
0001        50                       2
0002        150                      3

我该怎么做？谢谢大家的回答。

Answer 1

我已经找到解决方案了。这是我的:

SELECT q3.item_code, q3.item_name, substring_index(q3.AMOUNT_LIST, ',' , 1) as most_amount_count, 
substring_index(q3.count_list,',',1) as count_time from

(SELECT q2.item_code, q2.item_name, GROUP_CONCAT(SELL_DELIVERY_AMOUNT ORDER BY q2.count_amount DESC) as AMOUNT_LIST, 
GROUP_CONCAT(q2.count_amount ORDER BY q2.count_amount desc) as count_list from

    (SELECT  i.item_code, i.ITEM_NAME, s.SELL_DELIVERY_AMOUNT , count(s.SELL_DELIVERY_AMOUNT) as count_amount
    FROM sell_delivery_items s , items i
    WHERE s.item_id = i.item_id
    GROUP BY SELL_DELIVERY_AMOUNT, item_code
    order by item_code, count_amount desc
    )  as q2

group by q2.item_code )as q3