在按字母顺序排列搜索词之前和之后选择n行

问题描述：

Dilemma: I have a table with several hundred rows in it. I would like to submit a mysql_query with a specific search term wherein the query finds where the term would be located alphabetically in the column and then returns the n rows before the placement and n rows after the placement.

Example: Imagine that I have a column like the following (I'm placing it horizontally for the sake of space, but for sake of argument, let's pretend that this is a vertical list of column values):

|apple|asus|adder|billy|cat|dog|zebra|computer|mouse|cookie|donkey|

If I were to run the query on the term courage, assuming n = 3, I would like to have it return the following rows in this order:

|cat|computer|cookie|dog|donkey|mouse|

Alphabetically, the word courage would land right in the middle of those results and we are met with the preceding 3 entries and the following 3 entries.

Language Notes: I'm using php and mysql. I don't have any code to display because I'm not sure whether this needs to be in the where clause, or if it requires a subquery, or if you need to do something with the variable in php before handing it to the query.

困境： strong>我有一个包含数百行的表。我想提交一个带有特定搜索词的mysql_query，其中查询在列中按字母顺序查找术语的位置，然后在放置之前返回n行，在放置之后返回n行。 p>

示例： strong>想象一下，我有一个如下所示的列（我为了空间而将它水平放置，但为了争论，让我们假装这是一个列的垂直列表值）： p>

  |苹果|华硕|加法器|比利|猫|狗|斑马|计算机|鼠标|饼干|驴| 
 代码>  PRE>  
 
 如果我在术语 courage  code>上运行查询，假设n = 3，我想让它按此顺序返回以下行： p> \  n 
 
  | cat | computer | cookie | dog | donkey | mouse | 
  code>  pre> 
 
 按字母顺序，单词 courage  code  >将在这些结果的中间着陆，我们会遇到前面的3个条目和以下3个条目。 p> 
 
 
 语言注释： strong>我在使用 g php和mysql。 我没有要显示的代码，因为我不确定这是否需要在 where  code>子句中，或者它是否需要子查询，或者是否需要对变量执行某些操作 php在将其交给查询之前。   p> 
  div>

答

You might try an UNION of two SELECTS. Or do it manually.

SELECT term FROM table WHERE term < 'courage' ORDER BY term DESC LIMIT 3;

will return cookie, computer and cat (in descending order).

Then SELECT term FROM table WHERE term >= 'courage' ORDER BY term ASC LIMIT 3;

will return dog, donkey and mouse.

In PHP, you get the two sets, reverse the first and join.

A wholly-SQL solution might be

SELECT term FROM (
    SELECT term FROM table WHERE term < 'courage' ORDER BY term DESC LIMIT 3 )
    UNION
    SELECT term FROM table WHERE term >= 'courage' ORDER BY term ASC LIMIT 3 )
) ORDER BY term;

在按字母顺序排列搜索词之前和之后选择n行

相关推荐