直接上sql:
select c.* from (
SELECT
a.id,
a.longitude,
a.latitude,
b.state,
b.id as bid
FROM
`event`.t_room_info a left join event.t_active b on a.id = b.room_id and b.`status` = 1
WHERE
a.`status` = 1
AND a.storey_id = 12
order by a.id,b.state
) as c
GROUP BY c.id
估计大家都是用子查询解决方法如上,可是分组的取值结果却没有按照排序后取第一个,这里大家是不是很奇怪!!!(上面的方法在mysql5.7之前是可以使用的)原因是MySql 5.7对子查询进行了优化,认为子查询中的order by可以进行忽略,只要Derived table里不包含如下条件就可以进行优化:
1.UNION clause
2.GROUP BY
3.DISTINCT
4.Aggregation
5.LIMIT or OFFSET
如果大家对这有兴趣可以看下下面链接的详细解释
MySQL · 新特性分析 · 5.7中Derived table变形记
解决方法:大家可以在order by 后面补上limit这样就可以让子查询中的order by生效,sql如下:
select c.* from (
SELECT
a.id,
a.longitude,
a.latitude,
b.state,
b.id as bid
FROM
`event`.t_room_info a left join event.t_active b on a.id = b.room_id and b.`status` = 1
WHERE
a.`status` = 1
AND a.storey_id = 12
order by a.id,b.state asc limit 9999
) as c
GROUP BY c.id
备注:group by 的特性是分组后会取同一组的第一个值。所以可以利用分组去重