SQL 从使用（WHERE）条件选择的行中获取上一行和下一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27086377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get previous and next row from rows selected with (WHERE) conditions
提问by mahdi yousefi
For example I have this statement:
例如我有这样的声明：
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
我想得到每个单词的上一个和下一个单词
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?
我怎么能得到这个结果？
回答by a_horse_with_no_name
you didn't specify your DBMS, so the following is ANSI SQL:
您没有指定您的 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
SQLFiddle：http://sqlfiddle.com/#!12/7639e/1
回答by Anonymous Boar
Why did no-body give the simple answer?
为什么没有人给出简单的答案？
SELECT LAG(word) OVER ( ORDER BY ID ) AS PreviousWord ,
word ,
LEAD(word) OVER ( ORDER BY ID ) AS NextWord
FROM words;
回答by Fermat's Little Student
Without subqueries:
没有子查询：
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
回答by Saravana Kumar
Use Jointo get the expected result for SQL Server 2005plus.
使用Join以获得预期的结果为SQL Server 2005正。
create table words (id integer, word varchar(20));
insert into words
values
(1 ,'my'),
(2 ,'name'),
(3 ,'is'),
(4 ,'joseph'),
(5 ,'and'),
(6 ,'my'),
(7 ,'father'),
(8 ,'name'),
(9 ,'is'),
(10,'brian');
SELECT A.Id , C.word AS PrevName ,
A.word AS CurName ,
B.word AS NxtName
FROM words AS A
LEFT JOIN words AS B ON A.Id = B.Id - 1
LEFT JOIN words AS C ON A.Id = C.Id + 1
WHERE A.Word = 'name'
Result:
结果：
回答by P?????
Try this
尝试这个
SELECT *
FROM tablename a
WHERE ID IN(SELECT ID - 1
FROM tablename
WHERE word = 'name') -- will fetch previous rows of word `name`
OR ID IN(SELECT ID + 1
FROM tablename
WHERE word = 'name') -- will fetch next rows of word `name`
OR word = 'name' -- to fetch the rows where word = `name`
回答by mlinth
Here's a different approach, if you want the selects to be fast. It takes a bit of preparation work.
如果您希望选择快速，这是一种不同的方法。这需要一些准备工作。
Create a new column (e.g. "phrase") in the database that will contain the words you want. (i.e. the previous, the current and next).
Write a trigger that on insert appends the new word to the previous row's phrase and prepends the previous row's word to the new row's word and fills phrase.
If the individual words can change, you'll need a trigger on update to keep the phrase in sync.
在数据库中创建一个新列（例如“短语”），其中将包含您想要的单词。（即上一个，当前和下一个）。
编写一个触发器，在插入时将新单词附加到前一行的短语并将前一行的单词添加到新行的单词并填充短语。
如果单个单词可以更改，则需要在更新时触发以保持短语同步。
Then just select the phrase. You get much better speed, but at the cost of extra storage and slower insert and harder maintainability. Obviously you have to update the phrase column for the existing records, but you have the SQL to do that in the other answers.
然后只需选择短语。你获得了更好的速度，但代价是额外的存储和更慢的插入和更难的可维护性。显然，您必须更新现有记录的短语列，但您可以在其他答案中使用 SQL 来执行此操作。
回答by mahdi yousefi
I create index on my words column and set this code to get result quickly:
我在我的 words 列上创建索引并设置此代码以快速获得结果：
WITH CTE AS
(SELECT * FROM WordsTable WHERE word=N'Name')
SELECT
t2.word AS previousWord,
t1.word,
t3.word AS nextWord
FROM
WordsTable AS t2,
CTE AS t1,
WordsTable AS t3
WHERE
(t2.ID + 1)= t1.ID AND
(t3.ID - 1) = t1.ID
