mysql分组筛选数据库_mysql 关联表的筛选分组取最大值

SELECT b2.id bid,b2.`start_date`,b2.`name`,d.* FROM

(

SELECT c.*,MAX(IFNULL(c.sdate,'0')) max_start FROM

(

SELECT a.id,a.`update_time`,b.id bid,b.a_id,b.start_date sdate,b.end_date edate

from agreement a left join bills b

on b.a_id = a.id and b.sell_user_id = a.sell_user_id

where a.sell_user_id = '160000001'

AND a.`status` = 2

AND a.`update_time` >= 1559318400

AND a.`update_time` <= 1567439999

AND IFNULL(b.start_date,'0') < a.end_date

ORDER BY bid DESC

) c GROUP BY c.id

) d LEFT JOIN bills b2 ON d.id = b2.a_id AND b2.start_date = d.max_start

HAVING IFNULL(b2.`start_date`,'0') < FROM_UNIXTIME(d.`update_time`, "%Y-%m-%d");

网上参考了一翻，还是觉得有点差异，

上面的查询语句可以简化为下面的模型：

select b2*,d.*

(

select c.*,MAX(c.date) max_start FROM

(

select * from table_a a left join table_b b on a.aid = b.bid where a.id = '160000001'

) c GROUP BY c.aid

) d left join table_b b2 ON d.id = b2.bid AND b2.date = d.max_start

关联表的分组取最大值的数据库语句

其他知识点：

为空替换为0，否则输出原值

IFNULL(c.sdate,'0')

``

时间戳替换为时间格式

FROM_UNIXTIME(d.update_time, "%Y-%m-%d")