现在有个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()：（低版本没有这个函数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结果如何根据某个字段取最新时间去重的文章就介绍到这了,更多相关SQL取最新时间去重内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
