命令mysql查询结果按降序排列

I am trying to create a table for marks and rank team in decreasing order. i tried this.

我正在尝试按降序为分数和等级组创建一个表格。我试着这一点。

$q = $db_con-> prepare("
SELECT @s:=@s+1 serial_number, t_name,name,marks FROM team,user,(SELECT @s:=0) AS s WHERE user.u_id=team.u_id ORDER BY marks DESC");

Above query displays result in DESC order of marks but serial numbers get altered. like..

上面的查询显示了标记的DESC顺序，但是序列号被修改。像. .

sr no    team name   marks

 3          A         30
 1          B         20
 2          C         10

How to get serial numbers in order?

如何将序列号排序?

2 个解决方案

#1

Try this:

试试这个:

SELECT @s:=@s+1 serial_number, t_name, name, marks 
FROM (SELECT t_name, name, marks 
      FROM team t INNER JOIN user u ON u.u_id=t.u_id 
      ORDER BY marks DESC
     ) AS A, (SELECT @s:=0) AS s;

#2

You need to assign the serial number in a subquery and then sort by the marks:

您需要在子查询中分配序列号，然后按标记进行排序:

select t.*
from (SELECT @s:=@s+1 as serial_number, t_name, name, marks
      FROM team join
           user
           on user.u_id=team.u_id cross join
           (SELECT @s:=0)s
    ) t
ORDER BY marks DESC;

However, you are not ordering by anything for the serial number. You really need to because the ordering of tables is indeterminate.

但是，您并没有对序列号进行任何排序。您确实需要这样做，因为表的顺序是不确定的。

I also fixed your join syntax to use proper ANSI standard joins.

我还修改了您的连接语法以使用适当的ANSI标准连接。

#1