如何获得具有相同id的结果中mysql的排名

发布于 2024-09-24 14:34:15 字数 327 浏览 4 评论 0原文

快速提问，我有一个消息表，每个消息都有时间戳、reply_id 和 topic_id。 Reply_ids 可以与其他消息相同，这意味着这些消息位于同一组回复中。当我选择带有最小时间戳的reply_id时，这意味着它是该组的第一条消息，我还想知道它在该topic_id的所有结果中的编号位置，例如。第三（3）第四（4）等有人知道如何做到这一点或有任何建议吗？这可以用纯sql来完成吗？

SELECT reply_id,min(timestamp) as min FROM messages
   WHERE reply_chunk_id = ?
     AND topic_id = ?

原文

Quick question, I have a table of messages each with a timestamp and a reply_id and a topic_id. Reply_ids can be the same as others, which means those messages are in the same group of replies. When I select a reply_id with the min timestamp, meaning it was the first message of that group, I would also like to know its numbered place within all results of that topic_id, ex. 3rd (3) 4th(4) etc. Anyone know how to do this or have any suggestions? Can this be done in pure sql?

SELECT reply_id,min(timestamp) as min FROM messages
   WHERE reply_chunk_id = ?
     AND topic_id = ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泛泛之交 2024-10-01 14:34:15

使用纯 SQL：

SELECT m.reply_id,
       MIN(m.timestamp) as min,
       (SELECT COUNT(*)
          FROM MESSAGES t
         WHERE t.id <= m.id) AS rank
  FROM MESSAGES m
 WHERE m.reply_chunk_id = ?
  AND m.topic_id = ?

仅当 replyid 是唯一值时才有效。如果 replyid 之前有重复的 replyid，则 COUNT 将会错过该内容。

MySQL 不支持的分析函数将是更好的选择。您可以使用 MySQL SELECT 语句中的变量重新创建功能。

Using pure SQL:

SELECT m.reply_id,
       MIN(m.timestamp) as min,
       (SELECT COUNT(*)
          FROM MESSAGES t
         WHERE t.id <= m.id) AS rank
  FROM MESSAGES m
 WHERE m.reply_chunk_id = ?
  AND m.topic_id = ?

This works only if replyid is unique values. If there's a duplicate replyid before the replyid, then the COUNT will miss that.

Analytic functions, which MySQL doesn't support, would be a better option. You can recreate the functionality using variables in the MySQL SELECT statements.

回复收藏 0 原文

~没有更多了~