MYSQL枚举：@rownum，奇偶记录

我问了一个关于为查询结果创建临时/虚拟 ID 的问题，mysql 和 php：查询结果的临时/虚拟 ID？ https://stackoverflow.com/questions/4063998/mysql-php-temporary-virtual-ids-for-query-results/4064066#4064066

我几乎通过这个链接得到了我想要的，http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/ http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/

我已经设法枚举每一行，

SELECT 
u.pg_id AS ID, 
u.pg_url AS URL,
u.pg_title AS Title,
u.pg_content_1 AS Content,
@rownum:=@rownum+1 AS rownum

FROM (
    SELECT pg_id, pg_url,pg_title,pg_content_1
    FROM root_pages

    WHERE root_pages.parent_id = '7'
    AND root_pages.pg_id != '7'
    AND root_pages.pg_cat_id = '2'
    AND root_pages.pg_hide != '1'

    ORDER BY pg_created DESC
) u,

(SELECT @rownum:=0) r

result,

ID  URL     Title   Content     rownum
53  a       x       x           1
52  b       x       x           2
43  c       x       x           3
41  d       x       x           4

但我怎样才能进一步处理它 - 我想只显示奇数或偶数记录，如下所示 - 有可能吗？

奇怪的记录，

ID  URL     Title   Content     rownum
53  a       x       x           1
43  c       x       x           3

甚至记录，

ID  URL     Title   Content     rownum
52  b       x       x           2
41  d       x       x           4

谢谢。

附注尽管我几乎得到了答案，但实际上我不太理解sql查询，例如，“u”和“t”是什么意思？

“u”和“t”是什么意思？

它们是表别名，因此在需要引用时不必指定表的完整名称。

要仅获取奇数记录，请使用：

SELECT x.*
  FROM (SELECT u.pg_id AS ID, 
               u.pg_url AS URL,
               u.pg_title AS Title,
               u.pg_content_1 AS Content,
               @rownum := @rownum + 1 AS rownum
          FROM root_pages u
          JOIN (SELECT @rownum := 0) r
         WHERE u.parent_id = '7'
           AND u.pg_id != '7'
           AND u.pg_cat_id = '2'
           AND u.pg_hide != '1'
      ORDER BY u.pg_created DESC) x
WHERE x.rownum % 2 != 0

要获取偶数记录，请使用：

SELECT x.*
  FROM (SELECT u.pg_id AS ID, 
               u.pg_url AS URL,
               u.pg_title AS Title,
               u.pg_content_1 AS Content,
               @rownum := @rownum + 1 AS rownum
          FROM root_pages u
          JOIN (SELECT @rownum := 0) r
         WHERE u.parent_id = '7'
           AND u.pg_id != '7'
           AND u.pg_cat_id = '2'
           AND u.pg_hide != '1'
      ORDER BY u.pg_created DESC) x
WHERE x.rownum % 2 = 0

解释

The %是 MySQL 语法中的模运算符——它返回除法的余数。例如，1 % 2 是 0.5，而 2 % 2 是零。然后在 WHERE 子句中使用它来过滤显示的行。