mysql 8 写法select
sub.*
from
(
select
row_number() over (partition by msum.username order by msum.update_time desc) rowId,
msum.id
, msum.username
, msum.nickname
, msum.email
, msum.mobile
, msum.create_time
, msum.update_time
,res.ousername
from
me_sso_user_modify msum
join
(
SELECT
t.username,t.email ousername
FROM
me_sso_user_modify t
where
t.email in
( '11@qq.com')
) res
on
msum.username = res.username
) sub
where sub.rowId = 1
mysql 7 以下写法
上面的 row_number() over (partition by 就只支持 mysql8 而且也不方便使用 去重 函数。 可以按照下面的写法来:select
* from
(
select
msum.id
, msum.username
, msum.nickname
, msum.email
, msum.mobile
, msum.create_time
, msum.update_time
,res.ousername
from
me_sso_user_modify msum
join
(
SELECT
t.username,t.email ousername
FROM
me_sso_user_modify t
where
t.email in
('111@163.com','222@163.com','556@164.com','555@164.com','557@164.com','1233@164.com')
) res
on
msum.username = res.username
GROUP BY msum.username, msum.update_time
order by
msum.update_time desc
) t
GROUP BY t.username
参考网友 `select * from (select *,product_code as code from product order by create_date desc) t group by code;`