mysql group_concat聚合分组

碰到一个需求，规则是要按照同一天，同一个医生，同一个患者，同一个检查项目实现查询出相同的订单，这个玩意使用内联加上group by可以实现，但是效率低的吓人，而且返回的效果也不尽人意，作为一个专业的程序员，这种写法肯定是不可取的，于是寻找了新的解决办法，写法就在下面主要是使用了group_concat这个函数，完美实现，

separator ',' 这个关键字是返回数据的分隔符号默认为，注意这个方法必须放到后面，不然就报错

select DATE_FORMAT(orders.created_at,'%Y%m%d') days,
group_concat(orders.id,orders.order_code,orders.patient_name order by orders.created_at asc separator ',') 
from orders 
left JOIN agent_orders on agent_orders.order_id = orders.id
where orders.id>0 and  orders.state <> 6 and orders.state in (1,2) 
and agent_orders.agent_id in (1, 2, 3, 5, 6, 7, 8, 9, 10, 21, 26, 29, 30, 31, 32, 33, 34, 35, 37, 38, 40, 42, 45, 51, 52, 53, 56, 58, 59, 63, 67, 69, 70, 71, 73, 74, 75, 76, 79, 81, 86, 87, 89, 91, 92, 93, 94, 95, 99, 100, 101, 103, 104, 105, 119, 120, 143, 156, 157, 197, 199, 207, 241, 244, 245, 259, 269, 270, 271, 272, 273, 282, 293, 294, 303) 
and orders.created_at BETWEEN '2016-05-012 00:00:00.000000' AND '2016-05-13 00:00:00.000000'  
group by days,orders.patient_name,orders.doctor_id,orders.project_id HAVING(count(*) > 1  )

这就是内联写法

date_now = Time.now.strftime("%Y-%m-%d")
        repeat_orders_ids = Order.find_by_sql("select * from orders as a where (select count(*) from orders 
                                        where patient_name = a.patient_name and doctor_id = a.doctor_id and project_id = a.project_id and
                                        DATE(created_at)='#{date_now}')>1 and DATE(created_at)='#{date_now}'").map(&:id)
        have_repeat_orders = Order.where(id: repeat_orders_ids)
        first_orders = have_repeat_orders.group("patient_name")
        repeat_orders_ids = (have_repeat_orders-first_orders).map(&:id)
        repeat_orders = Order.where(id: repeat_orders_ids)
        repeat_orders.each do |r|
          r.update_attributes(is_repeat: 1)
        end