确定MySQL中字符串第一次出现的位置？

发布于 2024-09-11 16:42:14 字数 561 浏览 2 评论 0原文

对于这样的表：

username        | time
---------------------------------------
animuson        | 0.678
aP*animuson     | 0.967
animuson        | 0.567
qykumsoy        | 0.876

我试图在MySQL中找到一种方法来按时间排序，然后找到用户名出现的位置，并且用户名只计算一次。因此，如果我搜索用户名“qykumsoy”，它应该返回 2，因为两个“animuson”时间都更快，但只有最快的一个才算数，然后“qykumsoy”是第二快的。我可以对它们进行分组和排序，但如何找到用户名所在的位置？我认为在 MySQL 中执行此操作可能比在 PHP 中循环所有结果更快，如果我错了，请纠正我。

到目前为止我的想法是：

SELECT * FROM `times` ORDER BY MIN(`time`) GROUP BY `username`

我对 MySQL 的经验不是很丰富，只有基本的东西。有什么想法吗？

原文

For a table like this:

username        | time
---------------------------------------
animuson        | 0.678
aP*animuson     | 0.967
animuson        | 0.567
qykumsoy        | 0.876

I'm trying to find a way in MySQL to order these by times and then find the position where username occurs and username is only counted once. So, if I'm searching for username 'qykumsoy', it should return 2, because both 'animuson' times are faster, but only the fastest one counts, then 'qykumsoy' is the next fastest after that. I can get them grouped and ordered, but how do I find what position that username is at? I figured just doing this in MySQL might be faster than looping through all the results in PHP, correct me if I'm wrong.

What I've thought of so far:

SELECT * FROM `times` ORDER BY MIN(`time`) GROUP BY `username`

I'm not very well experienced with MySQL, only the basic stuff. Any ideas?

分享到QQ

分享到微博

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

发布评论

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

桃扇骨 2024-09-18 16:42:15

你所追求的是与表中数据相关的排名 - MySQL 没有任何排名/分析/窗口功能，但你有两个选择 - 这个：

SELECT x.rank
   FROM (SELECT t.username, 
                         t.time,
                         (SELECT COUNT(*)
                              FROM TABLE y
                             WHERE y.time <= t.time) AS rank
                 FROM TABLE t) x
 WHERE x.username = 'qykumsoy'

...或使用变量：

SELECT x.rank
   FROM (SELECT t.username, 
                         t.time,
                         @rownum := @rownum + 1 AS rank
               FROM TABLE t
                  JOIN (SELECT @rownum := 0) r
         ORDER BY t.time DESC) x
 WHERE x.username = 'qykumsoy'

What you're after is a rank with relation to the data in the table - MySQL doesn't have any ranking/analytic/windowing functionality but you have two options - this:

SELECT x.rank
   FROM (SELECT t.username, 
                         t.time,
                         (SELECT COUNT(*)
                              FROM TABLE y
                             WHERE y.time <= t.time) AS rank
                 FROM TABLE t) x
 WHERE x.username = 'qykumsoy'

...or using a variable:

SELECT x.rank
   FROM (SELECT t.username, 
                         t.time,
                         @rownum := @rownum + 1 AS rank
               FROM TABLE t
                  JOIN (SELECT @rownum := 0) r
         ORDER BY t.time DESC) x
 WHERE x.username = 'qykumsoy'

回复收藏 0 原文

~没有更多了~