Order by:两列之间的差异

Question

我有一个关于sql查询的问题。我在表users中有两列：

Moneytotal和Moneythisweek

现在我想对差值的结果进行排序。Moneytotal和Moneythisweek之间的差距越大，排名越高。

通常我使用：

$lsel_rank = mysql_query("select * from users ORDER BY Moneytotal DESC");
$rank = mysql_fetch_array($lsel_rank);

但现在我想做一些类似的事情：

lsel_rank = mysql_query("select * from users ORDER BY Difference between Moneytotal         
AND Moneythisweek DESC");

有谁可以帮我？

Answer 1

SELECT * FROM `users` ORDER BY Moneytotal - Moneythisweek DESC

Answer 2

只需简单地使用这个

$lsel_rank = mysql_query("select *,'moneytotal - monethisweek' as difference from users ORDER BY difference DESC");

Answer 3

选项1：(按orderby进行计算)

SELECT * FROM `users` ORDER BY (Moneytotal - Moneythisweek) DESC;

选项2：(在order by中使用编号索引，并计算差异并使其在select查询的结果集中可用

SELECT (Moneytotal - Moneythisweek) difference, a.*  FROM `users` a ORDER BY 1 DESC;