首页 > 开发 > MySQL > 正文

Mysql如何先排序后去重

2017-09-08 08:50:38  来源:网友分享
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去重

解决方案

如果只是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