SQL检查相邻行的顺序 [英] SQL check adjacent rows for sequence

问题描述

我有一个表，其中包含一个id列(唯一，主要)，一个名称(不是唯一的-实际上，很可能重复)和一个带有0、1或2值的flag列.假设我重新排序使用命令

I have a table with an id column (unique, primary), a name (not unique--in fact, most likely repeated), and a flag column which has values 0, 1, or 2. Let's say I reorder the table using the command

选择ID，名称，标志ORDER BY名称，ID

SELECT id, name, flag ORDER BY name, id

我想使用SQL生成一个名称列表，当向下读取重新排序的行时，有两个相邻的行具有相同的名称和值0和1(按该顺序)的标志.另外，在该列表中，我想知道发生这种情况的两行的ID.如果发生多次，则应该有多行.

I want to produce using SQL a list of names where, when the rows in the reordering are read downward, there are two adjacent rows with the same name and flags of value 0 and 1 (in that order). Additionally, in that list, I want to have the ids of the two rows where this happened. If it happened more than once, there should be multiple rows.

例如，在下面

id name flag
4  Bob  0
5  Bob  2
6  Bob  1
1  Cathy  0
7  Cathy  1
3  David  0
2  Elvis  2
8  Elvis  0
9  Elvis  1

我想选择

name id1 id2
Cathy 1 7
Elvis 8 9

我该怎么做?

我正在使用MySQL.

I'm using MySQL.

编辑:请注意，这些相邻行的ID可能不是连续的；仅当我们按名称订购时，它们才是连续的.例如，参见凯茜.

EDIT: Note that the IDs for those adjacent rows might not be consecutive; they're only consecutive if we order by name. See, for example, Cathy.

谢谢！

推荐答案

尝试:

select t1.name, t1.id as id1,t2.id as id2 
from tablename t1
join tablename t2 
on t2.name = t1.name and t2.flag = t1.flag + 1 and t2.id = 
    (select min(t3.id) from tablename t3
     where t1.name = t3.name and t1.id < t3.id)

修改了加入t2的内容，以包括对子查询的查找

amended join to t2 to include lookup on subquery

这篇关于SQL检查相邻行的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！