场景:有时候我们需要取每类商品里的价格最高或者优先级最高的那一条,分组肯定使用group by
然后结合使用聚合函数min()或者max()对数据进行筛选
示例如下,具体场景为:
我们每件商品有申请记录,想取出每件商品的优先级最高的和申请的人数,则如下实现即可
SELECT
min( bmgi.MEMBER_GRADE ) AS grade,
bicd.ITEM_ID as itemId,
count( DISTINCT bicd.MEMBER_ID ) as count
FROM
base_item_condition_demand bicd
JOIN base_member_grade_info bmgi ON bicd.MEMBER_ID = bmgi.MEMBER_ID
WHERE
bicd.ITEM_ID IN ( 16733784, 16773748 )
and bicd.DEMAND_STATE=1
and bmgi.GRADE_STATE=1
GROUP BY
bicd.ITEM_ID;