SQL結果如何根據(jù)某個字段取最新時間去重
現(xiàn)在有個sql,如果“propertyId”相同,取“updateTime”時間最新的那條記錄,其他過濾掉。
原始SQL
SELECT A.id AS id, A.property_catalogue AS propertyCatalogue, A.create_time AS updateTime, A.create_user AS createUser, B.id AS propertyId, B.property_name AS propertyName, B.property_type AS propertyType, B.file_id AS fileId, B.p_property_id AS pPropertyId, B.ownership_type AS ownershipType, C.file_type AS fileType, C.file_size AS fileSize FROM ca_property_usage_log AS A LEFT JOIN ca_property_ownership AS B ON B.id = A.property_id LEFT JOIN ca_file_storage AS C ON B.file_id = C.id WHERE B.property_type = 0 AND B.is_retrieve = 0 AND B.update_time >= DATE_SUB( NOW(), INTERVAL 10 DAY ) AND A.create_user = 3
1.使用ROW_NUMBER():(低版本沒有這個函數(shù)MySQL8以上才有)
結果SQL
為了通過
propertyId
去重并獲取每個propertyId
對應的最新時間的記錄,可以使用窗口函數(shù)ROW_NUMBER()
來對每個分組進行排序,并在外部查詢中取出rn
列等于 1 的行,即每個分組中的第一行,也就是最新時間的那一行。------------------------------------------------------------------------------------------------------------------------
這里使用了窗口函數(shù)
ROW_NUMBER()
來對每個分組進行排序,并在外部查詢中取出rn
列等于 1 的行,即每個分組中的第一行,也就是最新時間的那一行。請注意,如果多條記錄具有相同的最新
updateTime
值,則此查詢中的WHERE rn = 1
語句將返回其中一條記錄。如果需要返回所有具有相同最新時間的記錄,則可以使用RANK()
或DENSE_RANK()
窗口函數(shù)代替ROW_NUMBER()
。
-- 現(xiàn)在有個sql,如果“propertyId”相同,取“updateTime”時間最新的那條記錄,其他過濾掉。 SELECT * FROM ( SELECT A.id AS id, A.property_catalogue AS propertyCatalogue, A.create_time AS updateTime, A.create_user AS createUser, B.id AS propertyId, B.property_name AS propertyName, B.property_type AS propertyType, B.file_id AS fileId, B.p_property_id AS pPropertyId, B.ownership_type AS ownershipType, C.file_type AS fileType, C.file_size AS fileSize, ROW_NUMBER() OVER ( PARTITION BY B.id ORDER BY A.create_time DESC ) AS rn FROM ca_property_usage_log AS A LEFT JOIN ca_property_ownership AS B ON B.id = A.property_id LEFT JOIN ca_file_storage AS C ON B.file_id = C.id WHERE B.property_type = 0 AND B.is_retrieve = 0 AND B.update_time >= DATE_SUB( NOW(), INTERVAL 10 DAY ) AND A.create_user = 1 ) AS T WHERE rn = 1;
2.使用子查詢:
結果SQL
這個查詢使用了兩個子查詢。第一個子查詢用來獲取每個
propertyId
對應的最新時間max_create_time
。第二個子查詢在外部查詢中使用了左連接,將T
子查詢中的propertyId
和max_create_time
與其他三個表連接,以獲取需要的數(shù)據(jù)。如果某個propertyId
沒有與T
子查詢中的任何一行匹配,則該propertyId
不會出現(xiàn)在結果集中。------------------------------------------------------------------------------------------------------------------------
請注意,在此查詢中,我們假設每個
propertyId
對應的記錄數(shù)量不會太大(例如小于幾千條)。如果每個propertyId
對應的記錄數(shù)量很大,則可能會影響查詢的性能。
SELECT A.id AS id, A.property_catalogue AS propertyCatalogue, A.create_time AS updateTime, A.create_user AS createUser, B.id AS propertyId, B.property_name AS propertyName, B.property_type AS propertyType, B.file_id AS fileId, B.p_property_id AS pPropertyId, B.ownership_type AS ownershipType, C.file_type AS fileType, C.file_size AS fileSize FROM ( SELECT A.property_id, MAX(A.create_time) AS max_create_time FROM ca_property_usage_log AS A LEFT JOIN ca_property_ownership AS B ON B.id = A.property_id WHERE B.property_type = 0 AND B.is_retrieve = 0 AND B.update_time >= DATE_SUB(NOW(), INTERVAL 10 DAY) AND A.create_user = 3 GROUP BY A.property_id ) AS T LEFT JOIN ca_property_usage_log AS A ON T.property_id = A.property_id AND T.max_create_time = A.create_time LEFT JOIN ca_property_ownership AS B ON B.id = A.property_id LEFT JOIN ca_file_storage AS C ON B.file_id = C.id WHERE B.property_type = 0 AND B.is_retrieve = 0 AND B.update_time >= DATE_SUB( NOW(), INTERVAL 10 DAY ) AND A.create_user = 3
總結
到此這篇關于SQL結果如何根據(jù)某個字段取最新時間去重的文章就介紹到這了,更多相關SQL取最新時間去重內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
如何用workbench導出mysql數(shù)據(jù)庫關系圖
用workbench導出mysql數(shù)據(jù)庫關系圖的解決方法,需要的朋友請往下閱讀2013-03-03