mysql - 一个去重SQL的思路

Question

某用户主页访问表visit，用来记录用户个人主页被访问的记录。
字段有：

id user_id_from user_id_to time

现有查看用户主页访问记录的需求，去重需求为只保存某天内同一用户的最新一条的记录。
大家帮忙给给写这条SELECT语句的思路。

Answer 1

Well, I have solved it and posted it for everyone to take a look at. The key is to ORDER BY date and person at the same time. After writing it, I found that it is very simple...

    <select id="getUserVisitDoListDistinct" parameterClass="java.util.Map" resultMap="userVisitDoResult">
        SELECT
            id,
            user_id_to,
            user_id_from,
            status,
            MAX(createtime) AS createtime 
        FROM
            (select * from dx_user_visit ORDER BY createtime DESC) AS foo 
        WHERE user_id_to = #user_id_to# AND user_id_from != #user_id_to#
        GROUP BY (DATE_FORMAT(createtime,"%Y%m%d")),user_id_from
        <isGreaterThan prepend="HAVING" property="lastId" compareValue="0">
           <![CDATA[
               id < #lastId# 
           ]]> 
        </isGreaterThan>
        ORDER BY createtime DESC
        LIMIT 0,#count#
    </select>

Answer 2

select v1.* from visit as v1
right join (
select id, max(createtime) as ctime from visit group by id
) as v2 on v1.id=v2.id and v1.createtime=v2.ctime

Answer 3

select v1.* from visit as v1
right join (
select vv1.id, max(vv1.createtime) as ctime from visit as vv1
inner join visit as vv2 on vv1.DATE_FORMAT(createtime,"%Y%m%d")=vv2..DATE_FORMAT(createtime,"%Y%m%d")
group by vv1.id
) as v2 on v1.id=v2.id and v1.createtime=v2.ctime

Answer 4

select user_id_from, min(createtime) last_visit_time from tablename group by user_id_from order by last_visit_time limit num;

Answer 5

How about selecting max time from tb group by usrid