SQL结果如何根据某个字段取最新时间去重

2023-06-10 08:47

短信预约 -IT技能 免费直播动态提醒

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

SQL结果如何根据某个字段取最新时间去重

1.使用ROW_NUMBER()：（低版本没有这个函数MySQL8以上才有）

结果SQL

为了通过 propertyId 去重并获取每个 propertyId 对应的最新时间的记录，可以使用窗口函数 ROW_NUMBER() 来对每个分组进行排序，并在外部查询中取出 rn 列等于 1 的行，即每个分组中的第一行，也就是最新时间的那一行。

------------------------------------------------------------------------------------------------------------------------

这里使用了窗口函数 ROW_NUMBER() 来对每个分组进行排序，并在外部查询中取出 rn 列等于 1 的行，即每个分组中的第一行，也就是最新时间的那一行。

请注意，如果多条记录具有相同的最新 updateTime 值，则此查询中的 WHERE rn = 1 语句将返回其中一条记录。如果需要返回所有具有相同最新时间的记录，则可以使用 RANK() 或 DENSE_RANK() 窗口函数代替 ROW_NUMBER()。

-- 现在有个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 与其他三个表连接，以获取需要的数据。如果某个 propertyId 没有与 T 子查询中的任何一行匹配，则该 propertyId 不会出现在结果集中。

------------------------------------------------------------------------------------------------------------------------

请注意，在此查询中，我们假设每个 propertyId 对应的记录数量不会太大（例如小于几千条）。如果每个 propertyId 对应的记录数量很大，则可能会影响查询的性能。

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结果如何根据某个字段取最新时间去重