分组取最新的一条数据

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

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