MySQL根据行号将表连接到自身不返回任何数据 [英] MySQL join table to itself based on row number returns no data

问题描述

我有一个带有ID列的日志表，我想执行以下操作:

I've got a logs table with an ID column in it, and I want to do the following:

• 选择ID和行号
• 根据行号将表连接到自身

我的目标是获取特定行以及之前的行.下面的SQL查询实际上什么也不返回.就像它完全忘记了行号.加入ID返回就可以了.

My objective is to get a specific row as well as the row before it. The SQL query below actually returns nothing. It's like it forgets the row number entirely. Joining on ID returns just fine.

在MySQL中是否需要做一些事情来支持这种操作?

Is there something that needs to be done in MySQL to support this kind of operation?

select * from 
    (select id, @rownum := @rownum + 1 as 'row' 
        from logs, (select @rownum := 0) r order by id) a
join
    (select id, @rownum := @rownum + 1 as 'row' 
        from logs, (select @rownum := 0) r order by id) b
on a.row = b.row;

推荐答案

在第二个查询中更改变量名称，在两个查询中使用相同的变量会产生意外的结果

Change your variable name in second query, using same variable in both queries can produce unexpected results

select * from 
    (select id, @rownum := @rownum + 1 as 'row' 
        from logs, (select @rownum := 0) r order by id) a
join
    (select id, @rownum1 := @rownum1 + 1 as 'row1' 
        from logs, (select @rownum1 := 0) r order by id) b
on a.row = b.row1;

这篇关于MySQL根据行号将表连接到自身不返回任何数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！