sql同一张表分组，取到每组里最新的一条数据

最新推荐文章于 2024-07-25 19:14:08 发布

程序员hhhh

最新推荐文章于 2024-07-25 19:14:08 发布

阅读量1.1k

点赞数

文章标签： sql java

本文链接：https://blog.csdn.net/qq_39144393/article/details/106215661

版权

rank是给数据打标，1说明这是最新的数组

select
        result.start_city_id AS "startCityId",
        result.start_city_name AS "startCityName",
        result.start_district_id AS "startDistrictId",
        result.start_district_name AS "startDistrictName",
        result.start_address_detail AS "startAddressDetail",
        result.unload_city_id AS "unloadCityId",
        result.unload_city_name AS "unloadCityName",
        result.unload_district_id AS "unloadDistrictId",
        result.unload_district_name AS "unloadDistrictName",
        result.unload_address_detail AS "unloadAddressDetail",
        result.category_name AS "categoryName",
        result.weight AS "weight",
        result.upload_time AS "uploadTime",
        result.arrive_time AS "arriveTime",
        result.remarks AS "remarks",
        result.requirement AS "requirement",
        result.created_time AS "createdTime"
    from (
        SELECT
            a.*,
            count(1) AS "rank"
        FROM t_entrust_goods_source a
        LEFT JOIN t_entrust_goods_source b
        ON a.start_city_id = b.start_city_id AND a.start_district_id = b.start_district_id
        AND a.unload_city_id = b.unload_city_id AND a.unload_district_id = b.unload_district_id
        AND a.category_name = b.category_name AND a.weight = b.weight
        AND a.source_from = b.source_from AND a.cust_crm_user_id = b.cust_crm_user_id
        AND a.created_time &lt;= b.created_time AND b.valid = 1
        WHERE
        a.valid = 1
        and a.source_from = #{sourceFrom}
        and a.cust_crm_user_id = #{custCrmUserId}
        GROUP BY a.start_city_id, a.start_district_id, a.unload_city_id, a.unload_district_id, a.category_name, a.weight, a.created_time
    ) result
    where
        result.rank = 1
    ORDER BY result.created_time DESC