快捷導(dǎo)航

SQL結(jié)果如何根據(jù)某個(gè)字段取最新時(shí)間去重

更新時(shí)間：2023年06月09日 12:11:23 作者：一枚小蝸牛H

在日常的項(xiàng)目開發(fā)當(dāng)中,經(jīng)常會遇到獲取同一屬性相同的記錄,如何獲取記錄時(shí)間最新的那一條,下面這篇文章主要給大家介紹了關(guān)于SQL結(jié)果如何根據(jù)某個(gè)字段取最新時(shí)間去重的相關(guān)資料,需要的朋友可以參考下

原始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