分组取最新的一条数据

泗水六年

已于 2023-01-31 10:47:48 修改

阅读量1.1k

点赞数

分类专栏： java 文章标签： sql 数据库

于 2023-01-31 10:39:54 首次发布

本文链接：https://blog.youkuaiyun.com/Jackey1314007/article/details/128813283

版权

java 专栏收录该内容

65 篇文章

订阅专栏

##分组取最新的一条数据，错误

SELECT
	a.account_balance,
	a.deposit_amount,
	a.deposit_time,
	a.create_user,
	a.resident_id,
	b.resident_code,
	b.resident_name,
	b.area_id,
	c.area_name,
	a.create_time 
FROM
	revenue_resident_deposit a
	LEFT JOIN data_resident b ON a.resident_id = b.id
	LEFT JOIN data_area c ON c.id = b.area_id
GROUP BY resident_id 	
ORDER BY deposit_time	DESC

##方法一分组取最新的一条数据，可行，但LIMIT限定数量100000，数据数量需要计算获取

SELECT
	a.account_balance,
	a.deposit_amount,
	a.deposit_time,
	a.create_user,
	a.resident_id,
	b.resident_code,
	b.resident_name,
	b.area_id,
	c.area_name,
	a.create_time 
FROM
	(SELECT * FROM revenue_resident_deposit ORDER BY deposit_time DESC LIMIT 100000) as a
	LEFT JOIN data_resident b ON a.resident_id = b.id
	LEFT JOIN data_area c ON c.id = b.area_id
GROUP BY a.resident_id

##方法二分组取最新的一条数据，可行

SELECT
	a.account_balance,
	a.deposit_amount,
	a.deposit_time,
	a.create_user,
	a.resident_id,
	b.resident_code,
	b.resident_name,
	b.area_id,
	c.area_name,
	a.create_time 
FROM
	revenue_resident_deposit a
	LEFT JOIN data_resident b ON a.resident_id = b.id
	LEFT JOIN data_area c ON c.id = b.area_id
	LEFT JOIN (SELECT MAX(id) id FROM revenue_resident_deposit GROUP BY resident_id) d ON  d.id =a.id
WHERE
  a.id = d.id

##方法三分组取最新的一条数据，可行

SELECT
	a.account_balance,
	a.deposit_amount,
	a.deposit_time,
	a.create_user,
	a.resident_id,
	b.resident_code,
	b.resident_name,
	b.area_id,
	c.area_name,
	a.create_time 
FROM
	revenue_resident_deposit a
	LEFT JOIN data_resident b ON a.resident_id = b.id
	LEFT JOIN data_area c ON c.id = b.area_id
	LEFT JOIN (SELECT MAX(deposit_time) dt FROM revenue_resident_deposit GROUP BY resident_id) d ON  d.dt =a.deposit_time
WHERE
  a.deposit_time = d.dt