时间戳

首页 > 解决方案 > 如何从查询结果中过滤相邻行（第一、上一个、下一个、最后一个）？

subquery - 如何从查询结果中过滤相邻行（第一、上一个、下一个、最后一个）？

问题描述

我正在为大量记录开发快速链接解决方案。记录按用户排序（此处不相关的标准），我已经建立了 ID 列表。现在，我设法为每个 ID 添加了一个职位。

SELECT
     cp.id,
     cp.page_name,
     @rownum := @rownum + 1 AS `position`
FROM couch_pages cp
JOIN (SELECT @rownum := 0) r
WHERE cp.template_id = '2'
ORDER BY cp.publish_date DESC

表couch_pages有id、template_id、page_name和publish_date许多其他列。客户端可以请求任意顺序的 ID。我必须每次都跟随领先并重新计算位置。

我不知道是否可以不仅计算整个集合的位置，还可以获得任意 ID 的位置，例如，cp.id = '1600'并在单个查询中接收 prev-next-last-first 记录。

上面查询的结果看起来很简单——

"id"    "page_name" "position"

"1578"   "late-in-the-evening"  "1"
"1600"   "thats-why-god-made-"  "2"
"1599"   "god-bless-the-absen"  "3"
............................
"124601" "loves-me-like-a-ro"   "122703"

目前，我将整个结果发送到 PHP 数组并能够找到匹配的记录（例如，id="1600"）及其邻居 - 上一个记录（id="1578"）、下一个记录、第一个和最后的记录。

我不想将整个子集发送给客户端，因为我预计记录的数量会爆炸，脚本会变得更慢。

是否可以从结果中过滤给定 ID 的所需（上一个、下一个、最后一个、第一个）记录？

标签： subquerymysql-5.5

解决方案

使用Cross JOIN我为您的记录创建了一个新列，以确定哪个是第一条记录，哪个是最后一条记录。正如我在第一条评论中所述， acount(*)应该就足够了，因为我们可以对照该position列检查它。

在 MySQL 中，JOIN、CROSS JOIN和 INNER JOIN 是语法等价物（它们可以相互替换）。在标准 SQL 中，它们是不等价的。INNER JOIN 与 ON 子句一起使用，CROSS JOIN 用于其他情况。

试试下面的代码：

    SELECT
    a.*,
    (
    CASE

            WHEN a.position = 1 
            AND a.position = c.total_records THEN
                3 -- Mark only if you have one record 3 = (1 and 2) at the same time

                WHEN a.position = 1 THEN
                1 -- Mark first record with 1

                WHEN a.position = c.total_records THEN
                2 -- Mark last record with 2
                ELSE 0 
            END 
            ) AS row_status 
        FROM
            ( SELECT count( * ) AS total_records FROM couch_pages ) c
            CROSS JOIN (
            SELECT
                x.id,
                x.page_name,
                x.position 
            FROM
                (
                SELECT
                    t.id,
                    t.page_name,
                    @rownum := @rownum + 1 AS position 
                FROM
                    couch_pages t
                    JOIN ( SELECT @rownum := 0 ) r 
                -- ORDER BY
                    -- cast( SUBSTR( t.page_name, 5 ) AS INT ) -- Order by the INT part of your page_name

                ) x 
            ) a 
    LIMIT 4 OFFSET 3;-- This is the line you should adjust using PHP, you can keep the values in POST or GET like FQDN/?page=3&records_per_page=4

推荐阅读