mysql - 使用@rownum :=@rownum+1 AS rownum 枚举 SQL 中的每条记录

Question

我在 MySQL 中有这个查询

SELECT 
@rownum:=@rownum+1 AS rownum,
notifications_posts.from_user AS source,
notifications_posts.on_post_id AS destination,
notifications_posts.in_group_id,
groups.group_name AS group_name,
notifications_posts.on_post_id AS other_record_id,
user_info.first_name,
user_info.last_name,
user_info.user_id,
notifications_posts.date,
posts.title AS 'title',
user_rights.right AS 'right',
'article' AS notification_type
FROM notifications_posts
INNER
    JOIN user_info
    ON notifications_posts.from_user = user_info.user_id
INNER
    JOIN posts
    ON posts.id = notifications_posts.on_post_id
INNER
    JOIN groups
    ON groups.group_id = notifications_posts.in_group_id
INNER
    JOIN user_rights
    ON user_rights.group_id = notifications_posts.in_group_id AND user_rights.user_id = user_info.user_id

我明白了

为什么我在 rownum 处得到 NULL？

其次，我需要按该 id 对记录进行分组

GROUP BY rownum;

谁能帮帮我？

Answer 1

我认为最好只对结果进行排序(在 MySQL 中)，然后在您的应用程序中添加行号。如果您坚持:

警告:任何以这种方式使用变量的查询都可能在未来版本的 MySQL 中被破坏。

SELECT 
    @rownum := @rownum+1 AS rownum,
    notifications_posts.from_user AS source,
    notifications_posts.on_post_id AS destination,
    notifications_posts.in_group_id,
    groups.group_name AS group_name,
    notifications_posts.on_post_id AS other_record_id,
    user_info.first_name,
    user_info.last_name,
    user_info.user_id,
    notifications_posts.date,
    posts.title AS 'title',
    user_rights.right AS 'right',
    'article' AS notification_type
FROM 
    (SELECT @rownum:=0) AS dummy                  -- initial value
CROSS 
    JOIN notifications_posts
INNER
    JOIN user_info
    ON notifications_posts.from_user = user_info.user_id
INNER
    JOIN posts
    ON posts.id = notifications_posts.on_post_id
INNER
    JOIN groups
    ON groups.group_id = notifications_posts.in_group_id
INNER
    JOIN user_rights
    ON user_rights.group_id = notifications_posts.in_group_id 
    AND user_rights.user_id = user_info.user_id 
ORDER BY                                         -- you need 
    whatever ;                                   -- ORDER BY clause