通过MySQL窗口函数获取最新未读消息数据

最新推荐文章于 2025-12-12 18:30:32 发布

原创最新推荐文章于 2025-12-12 18:30:32 发布 · 121 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #未读消息 #GORM

WITH RankedMessages AS (
    SELECT 
        m.id, m.title, m.type, m.event_type, m.pusher_uid, 
        m.message, m.created_at, m.notifier_uid,
        ROW_NUMBER() OVER (PARTITION BY notifier_uid, type ORDER BY id DESC) as rn
    FROM messages m
    WHERE m.is_read = 0 
    AND m.deleted_at IS NULL
    -- AND notifier_uid IN (1, 2, 3, 4)  -- 可选：限制特定用户
)
SELECT id, title, type, event_type, pusher_uid, message, created_at, notifier_uid
FROM RankedMessages
WHERE rn = 1

GORM中使用：

showFields := []string{"id", "title", "type", "event_type", "pusher_uid", "notifier_uid", "message", "created_at"}
		subQuery := mysql.GetDbConnection().Debug().Model(msg).
			Select(append(showFields, "ROW_NUMBER() OVER (PARTITION BY type ORDER BY id DESC) as rn")).
			Where("is_read = ? AND notifier_uid = ?", 0, userId)

		lastMessages := make([]*model.Message, 0, len(messageCountTotal))
		err = mysql.GetDbConnection().Debug().Table("(?) as ranked_messages", subQuery).
			Select(showFields).
			Where("rn = 1").
			Scan(&lastMessages).Error