mysql group by获取第一组数据的sql
SELECT
h_m_r_r.*, o.`short_name`
FROM
house_meter_reading_record h_m_r_r,
`options` o
WHERE
o.id = h_m_r_r.type
AND h_m_r_r.id IN (
SELECT
SUBSTRING_INDEX(
GROUP_CONCAT(
h_m_r_r.id
ORDER BY
h_m_r_r.create_time DESC
),
',',
1
)
FROM
house_meter_reading_record h_m_r_r,
`options` o
WHERE
h_m_r_r.house_id = ?
AND h_m_r_r.flag = 0
AND o.id = h_m_r_r.type
GROUP BY
type
)
原理就是 用子查询 id order by 最新的时间 , 然后拼接id,然后用substring_index拿第一个id(这个id就是第一组数据的id) 然后用外面的嵌套 in查询查里面的id的集合