mysql - 使用 WHERE 子句查询与前一个匹配行的差异

Question

为了区分一行与前一行，我正在JOIN将一个表连接到自身并与之前的id(主键)进行比较:

CREATE TABLE `tablename` (
    id INT(5) PRIMARY KEY AUTO_INCREMENT,
    data INT
);

SELECT t1.id, t1.data, t1.data-(SELECT data FROM tablename WHERE id=t1.id-1) AS diff
FROM tablename t1 LEFT JOIN tablename t2 ON t1.id = t2.id
ORDER BY diff DESC LIMIT 1;

如何将其限制为仅特定的数据子集？例如，如果将列 user VARCHAR(32) 添加到表中，我如何限制查询以仅区分特定用户？向两个 SELECT 查询添加一个简单的 WHERE 子句将不起作用，因为整个前提取决于连续的 id。我想我可以将数据移动到一个临时表中，但如果有更清洁、更高效的解决方案，那么我会使用它。

我的意图是添加一个 WHERE user='someUser' 子句，diff 将只针对特定用户进行 diff，而不是整个表。

这是一个有效的 sqlfiddle .我想修改此查询，使其仅适用于 WHERE name='a'，这样结果就是 id=9, data=305, diff=125.

Answer 1

给你:

SELECT id, data, data - COALESCE(previous, data) as diff FROM (
SELECT
t.*,
@prev AS previous,
@prev:=t.data 
FROM
tablename t
, (SELECT @prev:=NULL) var
where name = 'a'
)sq
order by diff desc
limit 1

解释:

, (SELECT @prev:=NULL) var

变量的初始化。

@prev AS previous,
@prev:=t.data 

在第一列输出变量的当前值，在第二列分配当前行的值。

COALESCE(previous, data)

如果 previous 为 NULL，则返回列数据的值。因此，没有前一行的行的差异为零。

如果您有更多问题，请随时提出。

附注:此查询甚至可能比您必须自行加入的查询更快。