一个常见问题 这类问题有一个名字 greatest-n-per-group 可以在stackOverFlow上看到大量讨论

分组中某栏最大好取 只要

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

若是要完整记录呢？
有两种方法 一种inner join 一种left join inner似乎快一点？

1
2
3
4
5
6
7
8


--标准答案之inner join 超级快

select * from automated_task_center_test_wxj.atc_machine_heartbeat_logs t1 
                                     join           (select max(heartbeat) a, machine_id b from automated_task_center_test_wxj.atc_machine_heartbeat_logs group by b)
                                                    t on t1.heartbeat=t.a and t1.machine_id=t.b
order by machine_id
1
2
3
4
5
6
7
8


--标准答案之inner join的另一种写法 和innerjoin语义上是等价 

select * from automated_task_center_test_wxj.atc_machine_heartbeat_logs t1,
                                                (select max(heartbeat) a, machine_id b from automated_task_center_test_wxj.atc_machine_heartbeat_logs group by b)
                                                    t where t1.heartbeat=t.a and t1.machine_id=t.b
order by machine_id

插播1

为什么等价？
请教inner join 与一般笛卡尔积的区别，如select * from tb1 a inner join tb2 b on a.id=b.id 与select * from tb1 a ,tb2 b where a.id=b.id 有什么不同？谢谢！
答案：等价

笛卡尔积是怎么回事？{a,b},{0,1,2}->{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

mysql笛卡尔积是什么写法？
笛卡尔积指的是交叉连接，交叉连接的语法格式如下：
SELECT <字段名> FROM <表1> CROSS JOIN <表2>
或
SELECT <字段名> FROM <表1>, <表2>
两者等价

为什么笛卡尔积不合理？

为了避免这种情况的发生，可以采用等值连接的方法

带等值连接的笛卡尔积结果=内联

插播2
我这个场景其实只需要 因为id是唯一的 且id大日期一定大 巧妙做法 但是如果需要的最大项不唯一且跟id没规律就不能这么用了

1
2
3
SELECT *
FROM automated_task_center_test_wxj.atc_machine_heartbeat_logs
WHERE id in (SELECT MAX(id) FROM automated_task_center_test_wxj.atc_machine_heartbeat_logs GROUP BY machine_id)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--标准答案之left join 不咋快

SELECT a.*
FROM automated_task_center_test_wxj.atc_machine_heartbeat_logs a
LEFT  JOIN automated_task_center_test_wxj.atc_machine_heartbeat_logs b
    ON a.machine_id = b.machine_id AND a.heartbeat < b.heartbeat
WHERE b.machine_id IS NULL;



-- 为什么left join这么写是对的？How it works:
-- It matches each row from o with all the rows from b having the same value in column Group and a bigger value in column Age. Any row from o not having the maximum value of its group in column Age will match one or more rows from b.

-- The LEFT JOIN makes it match the oldest person in group (including the persons that are alone in their group) with a row full of NULLs from b ('no biggest age in the group').
-- Using INNER JOIN makes these rows not matching and they are ignored.

-- The WHERE clause keeps only the rows having NULLs in the fields extracted from b. They are the oldest persons from each group.
1
2
3
4

-- 这个写法貌似也对？我理解不了这个写法 貌似和经典inner等价 https://stackoverflow.com/questions/8470311/get-the-latest-date-from-grouped-mysql-data
SELECT d.machine_id, d.heartbeat, d.cpu, d.memory FROM automated_task_center_test_wxj.atc_machine_heartbeat_logs d
WHERE d.heartbeat IN (SELECT max(d2.heartbeat) FROM automated_task_center_test_wxj.atc_machine_heartbeat_logs d2 WHERE d2.machine_id=d.machine_id)
1
2
3
4
5
6
--理解不了的写法二  和上面有点像 来自思否中Ewellyuan被质疑的回答  https://segmentfault.com/q/1010000004138670
select *
from automated_task_center_test_wxj.atc_machine_heartbeat_logs as u
where u.heartbeat = (select max(heartbeat)
                     from automated_task_center_test_wxj.atc_machine_heartbeat_logs
                     where machine_id = u.machine_id)

https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

https://segmentfault.com/a/1190000004157112

https://stackoverflow.com/a/7745635