从使用(WHERE)条件选择的行中获取上一行和下一行 [英] Get previous and next row from rows selected with (WHERE) conditions

问题描述

例如我有这个声明:

my name is Joseph and my father's name is Brian

这个语句是按词拆分的，如下表:

This statement is splitted by word, like this table:

------------------------------
|      ID      |   word      |
------------------------------
|       1      |   my        |
|       2      |   name      |
|       3      |   is        |
|       4      |   Joseph    |
|       5      |   and       |
|       6      |   my        |
|       7      |   father's  |
|       8      |   name      |
|       9      |   is        |
|       10     |   Brian     |
------------------------------

我想获取每个单词的上一个和下一个单词

I want to get previous and next word of each word

例如我想获取name"的上一个和下一个单词:

For example I want to get previous and next word of "name":

--------------------------
|    my    |  name  |  is |
--------------------------
| father's |  name  |  is |
--------------------------

我怎样才能得到这个结果?

How could I get this result?

推荐答案

你没有指定你的 DBMS，所以下面是 ANSI SQL:

you didn't specify your DBMS, so the following is ANSI SQL:

select prev_word, word, next_word
from (
    select id, 
           lag(word) over (order by id) as prev_word,
           word,
           lead(word) over (order by id) as next_word
    from words
) as t
where word = 'name';

SQLFiddle:http://sqlfiddle.com/#!12/7639e/1

这篇关于从使用(WHERE)条件选择的行中获取上一行和下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！