MYSQL enumeration: @rownum, odd and even records
I asked a question about creating temporary/ virtual ids for query results, mysql & php: temporary/ virtual ids for query results?
I nearly got I wanted with this link, http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/
I have managed to enumerate each row,
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
but how can I work on it a bit further - I want to display the odd or even records only like the ones below - is it possible?
odd records,
ID URL Title Content rownum
53 a x x 1
43 c x x 3
even records,
ID URL Title Content rownum
52 b x x 2
41 d x x 4
thank you.
p.s. I don't quite understand the sql query actually even though I almost got the answer, for instance, what do the 'u' and 't' mea开发者_Python百科n?
what do the 'u' and 't' mean?
They are table aliases, so you don't have to specify the entire name of the table when you need to make reference.
To get only the odd numbered records, use:
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
To get the even numbered records, use:
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
Explanation
The %
is the modulus operator in MySQL syntax -- it returns the remainder of the division. For example 1 % 2 is 0.5, while 2 % 2 is zero. This is then used in the WHERE clause to filter the rows displayed.
精彩评论