我有一个MySQL表(称为formstatus
),有4列:id
,form
,status
和timestamp
,id
作为主键,form
和status
一起唯一。我想获取每form
具有第二最新时间戳的行集(尤其是status
)。我试过了
SELECT form, status FROM (
SELECT form, status, timestamp
FROM formstatus
WHERE timestamp < max(timestamp)
GROUP BY form) as a
WHERE timestamp = max(timestamp)
GROUP BY form
但它不起作用,因为显然 where 子句中不允许聚合。那么,是否有一个查询将返回我正在寻找的信息?
试试这个查询 -
SELECT
t1.*, COUNT(*) pos
FROM formstatus t1
LEFT JOIN formstatus t2
ON t2.form = t1.form AND t2.timestamp >= t1.timestamp
GROUP BY
t1.form, t1.timestamp
HAVING
pos = 2;
生成此错误的原因是不能在 WHERE
子句中使用聚合。 尝试将条件移至HAVING
子句。
GROUP BY form
HAVING timestamp < max(timestamp)