要获得所需的结果,首先需要找到每个公司的最后时间戳和紧挨着最后的时间戳。下面的查询非常简单:
SELECT c.company, c.mts, max(l.ts) AS lts
FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
LEFT JOIN cq l
ON c.company = l.company AND c.mts > l.ts
GROUP BY c.company, c.mts;
现在您必须将此子查询与原始表联接以获得所需的结果:
SELECT c.company, l.quote, coalesce(l1.quote, 0),
(l.quote - coalesce(l1.quote, 0)) AS result
FROM (SELECT c.company, c.mts, max(l.ts) AS lts
FROM (SELECT company, max(ts) AS mts FROM cq GROUP BY company) AS c
LEFT JOIN cq l
ON c.company = l.company AND c.mts > l.ts
GROUP BY c.company, c.mts) AS c
LEFT JOIN cq AS l ON l.company = c.company AND l.ts = c.mts
LEFT JOIN cq AS l1 ON l1.company = c.company AND l1.ts = c.lts;
你可以在
SQL Fiddle
.
这个查询只使用标准的sql功能,应该可以在任何rdbms上运行。