问

从使用(WHERE)条件选择的行中获取上一行和下一行

晨曦dora 发布于 2022-12-10 05:31

例如,我有这样的声明:

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

此语句按字分割,如下表所示:

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

我想得到每个单词的上一个和下一个单词

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

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

我怎么能得到这个结果？

3 个回答

没有子查询:

SELECT a.word 
FROM my_table AS a
JOIN my_table AS b 
ON b.word = 'name' AND abs(a.id - b.id) <= 1
ORDER BY a.id

2022-12-11 02:03 回答

小玲的夏天_905_735_602

您没有指定DBMS,因此以下是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

2022-12-11 02:14 回答

为什么没有身体给出简单的答案？

SELECT LAG(word) OVER ( ORDER BY ID ) AS PreviousWord ,
       word ,
       LEAD(word) OVER ( ORDER BY ID ) AS NextWord
FROM   words;

2022-12-11 02:57 回答

杨子忧愁_347

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签