:-:  如圖中所示，`uid = 50222`的用戶在`2022-11-22`這一天連續簽到了10次。很明顯此表沒有**唯一索引**，但是代碼方面的原因暫不考慮（不是咱寫的）  ### 要求： 刪除重復數據，并且保留最新一條（用戶一天重復有十條，刪九留一） > PS：其實不難，由于沒有接觸過 **SQLServer**，還有其中涉及的幾個函數，所以記錄一下 ### 思路： 1. 查出所有重復簽到的數據 2. 查出重復簽到中最新的一條數據 3. 刪除 ~~~ -- 統計重復數據 SELECT convert(varchar(10), addtime,20) time, uid, COUNT(uid) count FROM wap_qda GROUP BY convert(varchar(10), addtime,20), uid HAVING count(uid) > 1 -- 重復簽到中最新的一條數據ID SELECT MAX(ID) ID FROM wap_qda GROUP BY convert(varchar(10), addtime,20), uid HAVING count(uid) > 1 -- 全部重復數據 SELECT a.* FROM wap_qda a, (SELECT convert(varchar(10), addtime,20) time, uid FROM wap_qda GROUP BY convert(varchar(10), addtime,20), uid HAVING count(uid) > 1) b WHERE convert(varchar(10), a.addtime,20) = b.time AND a.uid = b.uid -- 刪除 DELETE FROM wap_qda WHERE wap_qda.ID IN (SELECT a.ID FROM wap_qda a, (SELECT convert(varchar(10), addtime,20) time, uid FROM wap_qda GROUP BY convert(varchar(10), addtime,20), uid HAVING count(uid) > 1) b WHERE convert(varchar(10), a.addtime,20) = b.time AND a.uid = b.uid) AND wap_qda.ID NOT IN (SELECT MAX(ID) ID FROM wap_qda GROUP BY convert(varchar(10), addtime,20), uid HAVING count(uid) > 1 ) ~~~ - **CONVERT()** [CONVERT()](https://www.w3school.com.cn/sql/func_convert.asp) 函數是把日期轉換為新數據類型的通用函數 CONVERT(data_type(length),data_to_be_converted,style) *data\_type(length)* 規定目標數據類型（帶有可選的長度）。*data\_to\_be\_converted*含有需要轉換的值。*style*規定日期/時間的輸出格式。