SQL結(jié)果如何根據(jù)某個(gè)字段取最新時(shí)間去重
現(xiàn)在有個(gè)sql,如果“propertyId”相同,取“updateTime”時(shí)間最新的那條記錄,其他過濾掉。
原始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():(低版本沒有這個(gè)函數(shù)MySQL8以上才有)
結(jié)果SQL
為了通過
propertyId
去重并獲取每個(gè)propertyId
對應(yīng)的最新時(shí)間的記錄,可以使用窗口函數(shù)ROW_NUMBER()
來對每個(gè)分組進(jìn)行排序,并在外部查詢中取出rn
列等于 1 的行,即每個(gè)分組中的第一行,也就是最新時(shí)間的那一行。------------------------------------------------------------------------------------------------------------------------
這里使用了窗口函數(shù)
ROW_NUMBER()
來對每個(gè)分組進(jìn)行排序,并在外部查詢中取出rn
列等于 1 的行,即每個(gè)分組中的第一行,也就是最新時(shí)間的那一行。請注意,如果多條記錄具有相同的最新
updateTime
值,則此查詢中的WHERE rn = 1
語句將返回其中一條記錄。如果需要返回所有具有相同最新時(shí)間的記錄,則可以使用RANK()
或DENSE_RANK()
窗口函數(shù)代替ROW_NUMBER()
。
-- 現(xiàn)在有個(gè)sql,如果“propertyId”相同,取“updateTime”時(shí)間最新的那條記錄,其他過濾掉。 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.使用子查詢:
結(jié)果SQL
這個(gè)查詢使用了兩個(gè)子查詢。第一個(gè)子查詢用來獲取每個(gè)
propertyId
對應(yīng)的最新時(shí)間max_create_time
。第二個(gè)子查詢在外部查詢中使用了左連接,將T
子查詢中的propertyId
和max_create_time
與其他三個(gè)表連接,以獲取需要的數(shù)據(jù)。如果某個(gè)propertyId
沒有與T
子查詢中的任何一行匹配,則該propertyId
不會出現(xiàn)在結(jié)果集中。------------------------------------------------------------------------------------------------------------------------
請注意,在此查詢中,我們假設(shè)每個(gè)
propertyId
對應(yīng)的記錄數(shù)量不會太大(例如小于幾千條)。如果每個(gè)propertyId
對應(yīng)的記錄數(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
總結(jié)
到此這篇關(guān)于SQL結(jié)果如何根據(jù)某個(gè)字段取最新時(shí)間去重的文章就介紹到這了,更多相關(guān)SQL取最新時(shí)間去重內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Navicat for MySql可視化導(dǎo)入CSV文件
這篇文章主要為大家詳細(xì)介紹了Navicat for MySql可視化導(dǎo)入CSV文件,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-05-05如何用workbench導(dǎo)出mysql數(shù)據(jù)庫關(guān)系圖
用workbench導(dǎo)出mysql數(shù)據(jù)庫關(guān)系圖的解決方法,需要的朋友請往下閱讀2013-03-03MySQL數(shù)據(jù)庫主從復(fù)制與讀寫分離
大家好,本篇文章主要講的是MySQL數(shù)據(jù)庫主從復(fù)制與讀寫分離,感興趣的同學(xué)趕快來看一看吧,對你有幫助的話記得收藏一下,方便下次瀏覽2021-12-12MYSQL基礎(chǔ)之連接MYSQL、修改密碼、添加用戶
在這篇文章中我們就從連接MYSQL、修改密碼、增加用戶等方面來學(xué)習(xí)一些MYSQL的常用命令。2008-08-08