Mysql如何先排序后去重

Question

SELECT c.*, d.devid
FROM coldbox20170906 c
LEFT JOIN coldbox b ON b.box_num = c.box_num
LEFT JOIN device d ON b.id = d.id
ORDER BY c.datatime DESC

如何根据box_num去重

Answer 1

如果只是coldbox20170906 表本身同一个box_num有多条记录，假设按datatime字段最小的为准，大概可以这样写：

SELECT c.*, d.devid
FROM (
    select *
    from coldbox20170906 c1 inner join (
      select box_num, min(datatime)
      from coldbox20170906 
      group by box_num
    ) c2 on c1.box_num = c2.box_num and c1.datatime = c2.datatime 
) c
LEFT JOIN coldbox b ON b.box_num = c.box_num
LEFT JOIN device d ON b.id = d.id