mysql – 排序表并获取行的位置
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 排序表并获取行的位置，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3790字，纯文字阅读大概需要6分钟。
内容图文
我有一个3行的表.播放器,时间,服务器.
现在,我想要两件事,我不知道怎么做.
对于服务器,有3个值得. 1,2和3.服务器和播放器是主键.用户是VARCHAR,服务器和时间是字符串.我用1,2和3让我更容易理解.
第一,我想按时间对表格进行排序并获得特定玩家的位置,但仅限于1个服务器.
例：
User Time Server
2 10 1
1 50 1
2 60 3
1 20 2
1 100 3
2 70 2
现在,想要获得用户2具有的位置
>如果按时间排序,服务器= 1
>用户2将是2号
>用户1号码1
>如果server = 2
>用户2将是1
>用户1将是2
然后我想获得用户位置,如果我按用户的时间总和排序.
在这种情况下,用户1将是1(时间总和：170),用户2将是2(时间总和：100).
我希望你能帮帮我！
顺便说一下,我想在Java中使用它;)
?java4ever /罗宾
解决方法:
QUERY#1
position the user 2 has if I sort by time and server = 1
SET @given_user = 2;
SET @given_server = 1;
SET @pos = 0;
SELECT * FROM
(
SELECT (@pos:=@pos+1) pos,User
FROM playerstats
WHERE server = @given_server
ORDER BY time DESC
) A
WHERE User = @given_user;
QUERY#2
if I sort by the sum of the time of a user
SET @pos = 0;
SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
(
SELECT User,SUM(time) TotalUserTime
FROM playerstats
GROUP BY User
) A ORDER BY TotalUserTime DESC;
你的样本数据
mysql> DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)
mysql> USE mydb
Database changed
mysql> CREATE TABLE playerstats
-> (
-> User INT,
-> Time INT,
-> Server INT
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.25 sec)
mysql> INSERT INTO playerstats VALUES
-> (2, 10,1),
-> (1, 50,1),
-> (2, 60,3),
-> (1, 20,2),
-> (1,100,3),
-> (2, 70,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM playerstats;
+------+------+--------+
| User | Time | Server |
+------+------+--------+
| 2 | 10 | 1 |
| 1 | 50 | 1 |
| 2 | 60 | 3 |
| 1 | 20 | 2 |
| 1 | 100 | 3 |
| 2 | 70 | 2 |
+------+------+--------+
6 rows in set (0.01 sec)
mysql>
QUERY#1 EXECUTED
mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @given_server = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM
-> (
-> SELECT (@pos:=@pos+1) pos,User
-> FROM playerstats
-> WHERE server = @given_server
-> ORDER BY time DESC
-> ) A
-> WHERE User = @given_user;
+------+------+
| pos | User |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.09 sec)
mysql>
QUERY#2 EXECUTED
mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
-> (
-> SELECT User,SUM(time) TotalUserTime
-> FROM playerstats
-> GROUP BY User
-> ) A ORDER BY TotalUserTime DESC;
+------+------+---------------+
| pos | User | TotalUserTime |
+------+------+---------------+
| 1 | 1 | 170 |
| 2 | 2 | 140 |
+------+------+---------------+
2 rows in set (0.00 sec)
mysql>
试试看 ！！！
更新2014-12-26 15:32美国东部时间
QUERY#3
SET @given_user = 2;
SET @pos = 0;
SELECT * FROM
(
SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
(
SELECT User,SUM(time) TotalUserTime
FROM playerstats
GROUP BY User
) A ORDER BY TotalUserTime DESC
) AA
WHERE User = @given_user;
QUERY#3 EXECUTED
mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM
-> (
-> SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
-> (
-> SELECT User,SUM(time) TotalUserTime
-> FROM playerstats
-> GROUP BY User
-> ) A ORDER BY TotalUserTime DESC
-> ) AA
-> WHERE User = @given_user;
+------+------+---------------+
| pos | User | TotalUserTime |
+------+------+---------------+
| 2 | 2 | 140 |
+------+------+---------------+
1 row in set (0.06 sec)
mysql>
内容总结
以上是互联网集市为您收集整理的mysql – 排序表并获取行的位置全部内容，希望文章能够帮你解决mysql – 排序表并获取行的位置所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。