如何查询出最大的记录

Question

现有用户订单表

用户表user
id  name
1    小明
2    小红



订单表order
id  user  time
1     1    1516758140
2     1    1516757140
3     1    1516756140
4     2    1516759140
5     2    1516758140
6     2    1516757140


如何查出小红跟小明时间戳最大的订单（最新的订单）在一个数组里 

Answer 1

select u.id,u.name,max(time) as mt from sg_order as o left join sg_user as u on o.user = u.id group by u.id
Array
(
    [0] => Array
        (
            [0] => 1
            [1] => 小明
            [2] => 1516759140
        )

    [1] => Array
        (
            [0] => 2
            [1] => 小红
            [2] => 1516755140
        )

    [2] => Array
        (
            [0] => 3
            [1] => 小张
            [2] => 1516757140
        )

)

表数据

Answer 2

1.用max函数查询t_order表（我加了前缀，user表也是）并根据user分组，获取最大的time数据
2.以步骤一的结果作为查询条件，进行子查询

字段名称、表明有所调整，不要在意这些细节

select * from t_order o where EXISTS (select 1 from
(select max(t.time) tm,t.user_id uid from t_order t group by t.user_id) f
where o.time = f.tm and o.user_id = f.uid);

Answer 3

你的最大是什么意思？最新的那条订单？

Answer 4

https://segmentfault.com/q/10...

Answer 5

用max函数或者查询的时候time倒序排列取一条，分别查出小明和小红的数据,然后再把他们放到一个数组中.

Answer 6

select * from order where time in(SELECT max(time) FROM order group by user);

Answer 7

select t1.*,t3.name from order t1
left join order t2 on t1.user=t2.user and t1.time<t2.time
left join user t3 on t1.user=t3.id
where t2.id is null