doudao2954
doudao2954
2018-06-11 16:18

Mysql查询按组最新日期搜索

已采纳

This is my table structure

enter image description here

and this is my dataset

enter image description here

What I want is query that gets data ordered by date desc and group by id_patient

so the result in the dataset example should be like this:

enter image description here

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • doudaiyao0934 doudaiyao0934 3年前

    I would go with limit clause with subquery since you have PK :

    select *
    from table t
    where id = (select t1.id
                from table t1
                where t1.id_patient = t.id_patient
                order by t1.date desc
                limit 1
               );
    

    However, if single patient has multiple same dates then this would produce only single records based on date.

    点赞 评论 复制链接分享
  • dov6891 dov6891 2年前
     SELECT *
     FROM table 
     GROUP BY group by id_patient 
     ordered by DATE(date) desc;
    
    点赞 评论 复制链接分享
  • dow46218 dow46218 3年前

    If you want the latest record for each patient, then you are not looking for an aggregation. I would often approach this with a correlated subquery:

    select t.*
    from t
    where t.date = (select max(t2.date) from t t2 where t2.id_patient = t.id_patient);
    
    点赞 评论 复制链接分享
  • dsaaqdz6223 dsaaqdz6223 3年前

    SELECT * from rdv a JOIN (SELECT id_patient,MAX(date) date FROM rdv GROUP by id_patient ) b on a.id_patient = b.id_patient and a.date = b.date

    点赞 评论 复制链接分享

相关推荐