mysql分组之后选取分组字段以外的字段以及选择组中特定的一行，附sql语句以及ActiveRecord查询语句

源自oa需求,系统中有两张表: 申请表 applies和 审批表audits,applies和audits是一对多的关系，也就是说一张申请单下面会对应多张审批单，但是有个时候会出现这种情况，同一个人在同一个申请单下会有多条审批单记录，即：这条申请单下有多级审批节点都是同一个人，如此一来，在该审批人的审批事务列表中就会出现多条对应于同一个申请单的审批单了，从数据表结构来分析的话，这其实是正常应该出现的情况，因为这个人的确是审批了多次，但是与现在需求要求不一致。

audits

id	apply_id	created_at	其他字段
1	1	2019-03-27 12:50:00
2	2	2019-03-27 12:50:01
3	2	2019-03-27 12:50:02
4	3	2019-03-27 12:50:03

思路

• 利用group by语句，按照 apply_id进行分组，但是这样有个限制，在sql中，select 后面跟随的字段必须是跟group by中进行分组的字段相关联的字段，只能选择与group by 相同的字段，若要选择其他未group by 的字段，则必须通过聚合函数合成为一个字段才能展示出来，比如:

select a from A group by a

或者

select a,sum(b) from A group by a

但是

当需要选择group by以外的字段的时候，就出现问题了，以上面的样表为例，我们不仅仅需要选择出apply_id,我们还需要选择出created_at以及其他的字段，也就是说，在group by分组完之后，还需要从组里面将其他的字段各选出一个值出来，比如，上面的表中如果group by 之后，数据应该先是这个样子的:

id	apply_id	created_at	其他字段
2	2	2019-03-27 12:50:01
3	2	2019-03-27 12:50:02

apply_id是一样的，但是created_at id 以及其他字段的值，是不一样的！但是我们最终的需要记录只要一条就够了！也就是还得继续从这个分出来的小组里面继续选出一条来。

最终解决方案

使用mysql的groupconcat函数配合substring_index函数

select distinct (a.apply_id), substring_index(group_concat(a.created_at order by a.id desc), ',', 1) ct 
from eams_edu3_test.audits a 
inner join eams_edu3_test.applies ap 
on ap.id = a.apply_id AND ap.school_id = 16 
WHERE a.`school_id` = 16 
group by a.apply_id LIMIT 11

groupconcat的作用即是将组中的该字段的值全部用,
拼接起来，这里还有一个小技巧，在groupconcat中还可以使用order by进一步进行排序再进行连接，然后使用substring_index截取第一个元素，这样即得到了我们所需要的元素。

另外：我的项目是使用的ruby语言，最终的ruby代码如下:

      @search = Audit.joins(:apply).where("applies.business_id != ? and applies.status != ?", Business::REVERT, Apply::STATE_REVERTED).where(auditor_id: current_user.id, available: true).ransack params[:q]
      @audits = @search.result.includes(apply: [:repair_sheet, :leave_sheet, :purchase_apply_sheet, :contract_sheet, :business]).includes(:applicant).
          select(:apply_id, "substring_index(group_concat(audits.id order by audits.created_at desc), ',', 1) id",
                 "substring_index(group_concat(audits.created_at order by audits.created_at desc), ',', 1) created_at",
                 "substring_index(group_concat(audits.status order by audits.created_at desc), ',', 1) status" ,
                 "substring_index(group_concat(audits.remark order by audits.created_at desc), ',', 1) remark",
                 "substring_index(group_concat(audits.stage order by audits.created_at desc), ',', 1) stage").
          group("audits.apply_id").
          order("field(substring_index(group_concat(audits.status order by audits.created_at desc), ',', 1), '#{Audit::UNREAD}') desc, created_at desc").
          page(params[:page] || 1).per(per_page)

参考网址:https://www.periscopedata.com/blog/selecting-only-one-row-per-group