SQLite/Java将每个名称的最新时间戳与24小时前的时间戳进行比较

Question

我试图从表中每个名称的最新时间戳和24小时前的时间戳中获取数据。我当前的方法进行两个单独的查询并组合结果。然而，这相当慢，也阻止了我对数据进行排序(通过注释等(

以下查询获取24小时前(最后一次(的数据

SELECT Price, comment ,name, timestamp
FROM details INNER JOIN car ON details .ID=car.ID
WHERE timestamp >= datetime('now','-1 day') and name = 'BMW'
order by timestamp asc limit 1

然后我有另一个类似的查询，它返回具有最新时间戳的数据(第一个(。

我在Java中有一个方法，它包含上述查询，并将一个新车名传递到name="部分。这会为每辆车返回第一个和最后一个，然后比较价格和评论细节并返回结果。

然而，事实证明这是一个非常缓慢的过程。这也意味着我无法有效地排序结果。

我也尝试过联合，但它确实提供了想要的结果

SELECT Price, comment ,name, max(timestamp)
FROM details INNER JOIN name ON details .ID=name .ID
UNION
SELECT Price, comment,name, min(timestamp)
FROM details  INNER JOIN name ON details .ID=name .ID
WHERE timestamp >= datetime('now','-1 day')
group by name
order by comment desc
limit 40

执行此查询的正确方法是什么？

Answer 1

要为每个名称获得一个输出行，请使用GROUP BY:

SELECT Price, comment, name, max(timestamp)
FROM details INNER JOIN name USING (ID)
GROUP BY name.ID
UNION ALL
SELECT Price, comment, name, min(timestamp)
FROM details INNER JOIN name USING (ID)
WHERE timestamp >= datetime('now','-1 day')
GROUP BY name.ID
ORDER BY ...;

Answer 2

假设cars.name是唯一的(即等效于cars.id，并且您希望结果在单独的行上，您可以在子查询中进行聚合以获得两个时间戳。然后，加入您想要的附加信息：

SELECT c.name, d.Price, d.comment, d.name, d.timestamp
FROM car c JOIN
details d 
ON d.ID = c.ID JOIN
(SELECT dd.ID, MAX(dd.timestamp) as maxts, MIN(dd.timestamp) as mints
FROM detail dd
WHERE dd.timestamp >= datetime('now', '-1 day')
GROUP BY dd.ID
) dd
ON dd.ID = c.ID AND d.timestamp IN (dd.mints, dd.maxts)
ORDER BY timestamp asc;