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

现在有个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