在另一个查询中引用MySQL子选择

I have a basic MySQL table, terms, comprised of an id and term field.

我有一个基本的MySQL表,术语,由id和term字段组成。

I want to create an alphabetically sorted dictionary index (in the literal sense), that would list ten 10 terms above the selected term, and 20 below it. An example of this could be found here http://www.urbandictionary.com/define.php?term=GD2&defid=3561357 where on the left column you see the current term highlighted, and a number of terms above it, and some below, all sorted alphabetically.

我想创建一个按字母顺序排序的字典索引(在字面意义上),它将在所选术语上方列出10个术语,在其下面列出20个术语。这方面的一个例子可以在http://www.urbandictionary.com/define.php?term=GD2&defid=3561357找到,在左栏中你看到当前的术语突出显示,以及上面的一些术语,以及下面的一些术语,全部按字母顺序排序。

As we all know, MySQL doesn't support a ROW_NUMBER() or a similar function so we end up resorting to user variables and sub-selects. I also cannot create a View with user defined variables because MySQL doesn't allow that. Here's what I managed to come up with (and it works):

众所周知,MySQL不支持ROW_NUMBER()或类似函数,所以我们最终会求助于用户变量和子选择。我也无法使用用户定义的变量创建一个View,因为MySQL不允许这样做。这是我设法提出的(并且它有效):

        SET @row_num := 0;

        SELECT
             @term_index := ordered.row_number
        FROM 
        (
            SELECT
                 @row_num := @row_num + 1 AS row_number, terms.*
            FROM
                terms
            ORDER BY
                term ASC
        ) AS ordered 
        WHERE
            ordered.term = 'example term';

        SET @row_num := 0;

        SELECT *
        FROM 
        (
            SELECT
                @row_num := @row_num + 1 AS row_number, terms.*
            FROM
                terms
            ORDER BY
                term ASC
        ) AS ordered
        WHERE
             row_number BETWEEN @term_index - 10 AND @term_index + 20

The first SELECT simply finds out the row number of our target term across the entire alphabetically sorted terms table. The second SELECT uses that information to get 10 terms above it and 20 terms below it.

第一个SELECT只是在整个按字母顺序排序的术语表中找出目标术语的行号。第二个SELECT使用该信息获得高于它的10个术语和低于它的20个术语。

I wonder if there's a way to avoid running the sub-select in the second SELECT query and instead just reference the first one aliased ordered. Is there a more efficient way of accomplishing this without having to resort to manually create a temporary table? What am I doing wrong here?

我想知道是否有办法避免在第二个SELECT查询中运行子选择,而只是引用第一个有序的别名。是否有更有效的方法来实现这一点,而无需手动创建临时表?我在这做错了什么?

1 个解决方案

#1

Update:

See this article in my blog for performance details:

有关性能详情,请参阅我的博客中的这篇文章

MySQL: selecting rows before and after filtered one

MySQL:在过滤之前和之后选择行

If your term is indexed, you can just run:

如果您的术语已编入索引,则可以运行:

SELECT  *
FROM    (
        SELECT  *
        FROM    terms
        WHERE   term <= @myterm
        ORDER BY
                term DESC
        LIMIT 10
        ) q
UNION ALL
SELECT  *
FROM    (
        SELECT  *
        FROM    terms
        WHERE   term > @myterm
        ORDER BY
                term
        LIMIT 20
        ) q
ORDER BY
        term

, which will be more efficient.

,这将更有效。

#1