在符合条件的行之前和之后选择SELECT N行? [英] SELECT N rows before and after the row matching the condition?

问题描述

我要复制的行为就像带有-A和-B标志的grep一样. 例如grep -A 2 -B 2 "hello" myfile.txt会给我所有其中有"hello"的行，但也给我前面的2行和后面的2行. 让我们假设这个表模式:

The behaviour I want to replicate is like grep with -A and -B flags . eg grep -A 2 -B 2 "hello" myfile.txt will give me all the lines which have "hello" in them, but also 2 lines before and 2 lines after it. Lets assume this table schema :

+--------+-------------------------+
| id     |    message              |
+--------+-------------------------+
| 1      | One tow three           |
| 2      | No error in this        |
| 3      | My testing message      |
| 4      | php module test         |
| 5      | hello world             |
| 6      | team spirit             |
| 7      | puzzle game             |
| 8      | social game             |
| 9      | stackoverflow           |
|10      | stackexchange           |
+------------+---------------------+

现在查询如下: Select * from theTable where message like '%hello%'将导致:

Now a query like : Select * from theTable where message like '%hello%' will result in :

5 | hello world

如何放置另一个参数"N"，该参数选择匹配记录的前N行，并在匹配记录后选择N行，即对于N = 2，结果应为:

How can I put another parameter "N" which selects N rows before, and N rows after the matched record i.e. for N = 2, the result should be :

    | 3      | My testing message      |
    | 4      | php module test         |
    | 5      | hello world             |
    | 6      | team spirit             |
    | 7      | puzzle game             |

• 为简单起见，假设"like％TERM％"仅匹配1行.
• 这里应该将结果按自动递增id字段排序.
• For simplicity assume 'like %TERM%' matches only 1 row .
• Here the result is supposed to be sorted on auto-increment id field.

推荐答案

对，这对我有用:

SELECT child.*
FROM stack as child,
(SELECT idstack FROM stack WHERE message LIKE '%hello%') as parent
WHERE child.idstack BETWEEN parent.idstack-2 AND parent.idstack+2;

这篇关于在符合条件的行之前和之后选择SELECT N行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！