下面这种方式绝对不是用子查询查出来的。可能效率上不是非常高,但是却有它的精妙之处!
mysql> select * from a;
------------------------------------------------
id | is_number_one | source | o1 | o2 | xxx | create_time |
------------------------------------------------
1 | 1 | 101 | 1.01 | 1.02 | 5 | 15:59:01 |
2 | 0 | 102 | 1.02 | 1.02 | 3 | 15:59:01 |
3 | 0 | 103 | 1.02 | 1.02 | 3 | 15:59:01 |
4 | 1 | 101 | 1.04 | 1.02 | 5 | 15:59:25 |
5 | 1 | 110 | 1.02 | 1.05 | 5 | 15:59:25 |
------------------------------------------------
不用子查询的方式查
mysql> select * from a a1 inner join a a2 on a1.is_number_one=a2.is_number_one and a1.source=a2.source where a1.is_number_one='1' and a1.source=101 group by a1.create_time having a1.create_time=max(a2.create_time);
----------------------------------------------------------------------------------------------+
id | is_number_one | source | o1 | o2 | xxx | create_time | id | is_number_one | source | o1 | o2 | xxx | create_time |
----------------------------------------------------------------------------------------------+
4 | 1 | 101 | 1.04 | 1.02 | 5 | 15:59:25 | 1 | 1 | 101 | 1.01 | 1.02 | 5 | 15:59:01 |
----------------------------------------------------------------------------------------------+
转载于:https://blog.51cto.com/ace105/731758