sql语句，查询数据库user_depts表中用户主科室最新一条数据，更改users表中的主科室dept_id

[likekaka]

查询数据库user_depts表中用户主科室最新一条数据： select from user_depts where id in (select SUBSTRING_INDEX(group_concat(id order by updated_at desc),',',1) from (select from user_depts where is_main = '1') user_depts group by user_id ); 更改users表中的主科室dept_id： update users as a, (select from user_depts where id in (select SUBSTRING_INDEX(group_concat(id order by updated_at desc),',',1) from (select from user_depts where is_main = '1') user_depts group by user_id )) as b set a.dept_id = b.dept_id where a.id = b.user_id;

这样有问题木有？

[RichardFans]

刘国外牛～

[alayang]

这个更新语句有点问题，可在本地测试一下，比如员工赵菁华，他的主科室为心内科，但执行此语句后，显示users表中为内一科

[likekaka]

试一试改成用created_at来排序筛选 像这样更改users表中的主科室dept_id： update users as a, (select * from user_depts where id in (select SUBSTRING_INDEX(group_concat(id order by created_at desc),',',1) from (select * from user_depts where is_main = '1') user_depts group by user_id )) as b set a.dept_id = b.dept_id where a.id = b.user_id;