mysql 排序位置_mysql – 排序表并获取行的位置

我有一个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>

标签：java,mysql,join,table

来源： https://codeday.me/bug/20190806/1602296.html