先按照同种类型进行排序,并取id最新的一条
使用@equipment_id 和@num两个变量,第一个变量分类,第二个变量标记最新,第二,第三等等。
SELECT *, IF (
@equipment_id = equipment_id,
@num :=@num + 1,
@num := 1
) AS num,
@equipment_id := cast(equipment_id as char)
from cp_fermentation_batch
ORDER BY
equipment_id,row_id DESC
最后再取最新的一条,即num=1的数据即可
SELECT * from (
SELECT *, IF (
@equipment_id = equipment_id,
@num :=@num + 1,
@num := 1
) AS num,
@equipment_id := cast(equipment_id as char)
from cp_fermentation_batch
ORDER BY
equipment_id,row_id DESC
) tab where num =1