mysql - 一个去重SQL的思路

Question

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

id user_id_from user_id_to time

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

Answer 1

額，我已經解決了，貼給大家看一看，關鍵是同時ORDER BY日期和人，寫完了發現還是很簡單的...

    <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

從訪問中選擇v1.* 作為v1
右連接（
按id 從訪問群組中選擇id, max(createtime) 作為ctime
）作為v1.id=v2.id 和v1上的v2 .createtime=v2.ctime

Answer 3

從存取選擇v1.* 作為v1
右連接（
從存取中選擇vv1.id, max(vv1.createtime) 作為ctime 作為vv1
內部連接在vv1.DATE_FORMAT 上作為vv2 訪問（ createtime,"%Y%m%d")=vv2..DATE_FORMAT(createtime,"%Y%m%d")
group by vv1.id
) as v2 on v1.id= v2.id和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

select max time from tb group by usrid這樣如何