SELECT c.*, d.devidFROM coldbox20170906 cLEFT JOIN coldbox b ON b.box_num = c.box_numLEFT JOIN device d ON b.id = d.idORDER BY c.datatime DESC
如何根据box_num去重
解决方案
如果只是coldbox20170906 表本身同一个box_num有多条记录,假设按datatime字段最小的为准,大概可以这样写:
SELECT c.*, d.devidFROM ( 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 ) cLEFT JOIN coldbox b ON b.box_num = c.box_numLEFT JOIN device d ON b.id = d.id