如何在MySQL中选择相邻的列

Question

如何在MySQL中选择相邻的列

3

我的表格： 词汇表

    id          word
--------------------------
    1           hello
    2           hello
    3           how
    4           how
    5           how
    6           are
    7           hello
    8           hello
    9           are
    10          are
    11          are
    12          are
    13          hello

我想要执行以下SELECT查询：select id from vocabulary where id=$id and {所有与之相邻且相同单词的行}

注意：我需要同时满足两个条件：[id=$id]和[所有与之相邻且相同单词的行]

实际上，我需要执行类似以下三个例子的SELECT查询：

$id=1，结果为：1,2 // [1 为 $id=1] - [2 为与1相邻且相同单词的行]
$id=6，结果为：6 // [6 为 $id=6]
$id=10，结果为：9,10,11,12 // [10 为 $id=10] - [9,11,12 为与10相邻且相同单词的行]

- user4920811

这是一个“序列起始和结束”的问题。 - Strawberry

这是什么意思？这是不可能的吗？ - user4920811

1

不，这意味着你现在知道该去搜索什么。 - Strawberry

啊哈 :) 我谷歌了很多，但是我无法解决它 :( - user4920811

2个回答

3

这是一个使用变量的解决方案：

SELECT id, word
FROM (
  SELECT id,       
         @rnk:= CASE WHEN @word = word THEN @rnk 
                   ELSE @rnk + 1
              END AS rnk,
         @word:= word AS word
  FROM vocabulary, (SELECT @rnk:=0) as vars    
  ORDER BY id ) s
WHERE s.rnk = (
    SELECT rnk
    FROM (
      SELECT id,       
             @r:= CASE WHEN @w = word THEN @r 
                       ELSE @r + 1
                  END AS rnk,
             @w:= word AS word
      FROM vocabulary, (SELECT @r:=0) as vars    
      ORDER BY id ) t
    WHERE id = 10) -- 10 is equal to $id

SQL Fiddle演示

由于MySQL缺乏CTE，所以同一查询被重复两次。使用@rnk和@r变量来识别vocabulary表中连续word值的岛屿。

第二个查询选择岛屿值（例如，id = 10的@r = 5），而第一个查询则使用此值来选择属于同一岛屿的所有记录。

- Giorgos Betsos

你能简要描述一下吗？ - user4920811

@stack 我认为我已经给出了简短的描述。您可以尝试单独执行其中一个子查询，以查看它返回的内容。然后您会意识到@r、@rnk变量对于属于同一组值的所有记录都具有相同的值，也称为岛屿。 - Giorgos Betsos

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Strawberry · Accepted Answer

这里有一个基本的模式，你可以根据自己的目的进行调整...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,word VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1 ,'hello'),
(2 ,'hello'),
(3 ,'how'),
(4 ,'how'),
(5 ,'how'),
(6 ,'are'),
(7 ,'hello'),
(8 ,'hello'),
(9 ,'are'),
(10,'are'),
(11,'are'),
(12,'are'),
(13,'hello');

SELECT a.id start
     , MIN(c.id) end 
  FROM my_table a
  LEFT 
  JOIN my_table b 
    ON b.id = a.id - 1
   AND b.word = a.word
  LEFT 
  JOIN my_table c 
    ON c.id >= a.id
   AND c.word = a.word
  LEFT 
  JOIN my_table d 
    ON d.id = c.id + 1
   AND d.word = a.word
 WHERE b.id IS NULL 
   AND c.id IS NOT NULL
   AND d.id IS NULL
 GROUP 
    BY a.id; 

+-------+------+
| start | end  |
+-------+------+
|     1 |    2 |
|     3 |    5 |
|     6 |    6 |
|     7 |    8 |
|     9 |   12 |
|    13 |   13 |
+-------+------+

如McAdam331所建议的那样，扩展这个想法的一种方法如下：

SELECT *  
  FROM vocabulary 
  JOIN tmpTable 
 WHERE id BETWEEN tmpTable.start AND tmpTable.end 
  AND tmpTable.start = $id;