MySQL:基于GROUP BY选取每组内符合自定义条件记录的查询方法
按分组取weight最高记录的MySQL实现
- 两种基于
GROUP BY categoryID的实现方案,按需选择: - 方案1:返回所有匹配分组最高weight的记录
SELECT * FROM sample_table WHERE (categoryID, weight) IN ( SELECT categoryID, MAX(weight) FROM sample_table GROUP BY categoryID );
- 方案2:每个分组仅返回单条记录(并列最高weight时取id最小的),适配单条输出需求
SELECT * FROM ( SELECT * FROM sample_table ORDER BY weight DESC, id ASC ) AS sorted_table GROUP BY categoryID;
方案2的执行返回结果:
+----+------------+--------+ | id | categoryID | weight | +----+------------+--------+ | 2 | 1 | 5 | | 6 | 2 | 9 | | 7 | 3 | 5 | +----+------------+--------+
若你预期categoryID=3的记录weight为3,可调整插入语句中该分组的初始数据,上述查询逻辑会自动匹配对应分组的最高weight值。
内容的提问来源于stack exchange,提问作者Sandeep
相关产品推荐
相关产品推荐

