mysql left join 一条_mysql 取left join表中最近时间的一条数据

要求：根据状态，最新编辑时间排序

状态为表1中的用户状态，最新编辑时间为表2中最后一次编辑时间

表1为users, 表2为opt_user_log

例：

SELECT

u.id,

u.user_name,

u.last_upd_time AS audit_time,

u.state,

suo.opt_user_name

FROM

users AS u

LEFT JOIN (

SELECT

opt_user_name,

opt_type,

user_id

FROM

opt_user_log

WHERE

opt_type IN (1, 2)

ORDER BY

add_time DESC

) AS suo ON suo.user_id = u.id

WHERE

(1 = 1)

GROUP BY

u.id

ORDER BY

field(u.state, 1, 2, 3),

audit_time DESC;

改：

SELECT

u.id AS uid,

u.user_name,

u.last_upd_time AS audit_time,

u.state,

( SELECT opt_user_name FROM lg_shop_users_opt_log WHERE opt_type IN ( 1, 2 ) AND user_id = uid ORDER BY add_time DESC LIMIT 1 ) AS opt_user_name

FROM

lg_shop_users AS u

GROUP BY

u.id

ORDER BY

field( u.state, 1, 2, 3 ),

audit_time DESC;

SELECT

u.id AS uid,

u.user_name,

u.last_upd_time AS audit_time,

u.state,

( select SUBSTRING_INDEX(GROUP_CONCAT(opt_user_name order by add_time DESC),',',1) from lg_shop_users_opt_log where opt_type IN(1,2) AND user_id=uid) AS opt_user_name

FROM

lg_shop_users AS u

GROUP BY

u.id

ORDER BY

field( u.state, 1, 2, 3 ),

audit_time DESC;