由asc和desc命令没有给出正确的结果

I have a problem getting result in my query .

我在查询中获得结果时遇到问题。

id ,voteup , votedown

1  , 12     ,  7
2  ,  6     , 1 
3  ,  4     , 9 
4  ,  0     , 6

and my query is

我的查询是

 SELECT cm.id ,cm.voteup,cm.votedown  FROM Table1 cm  
 INNER JOIN ( select id ,MAX(voteup - votedown ) as maxe 
             from Table1 where voteup - votedown >= 5  group by id  ) tt
 on cm.id = tt.id
 ORDER BY cm.voteup DESC,cm.votedown asc,cm.id  limit 1

Im getting this result

我得到了这个结果

  id, voteup , votedown
   1, 12     ,  7

I want to select the max result which have voteup - votedown >= 5 first And if two result have same result i want to order the one which have less votedown

我想选择具有voteup的最大结果 - voteedown> = 5 first如果两个结果有相同的结果我想订购那个投票少的那个

Im wishing having this result

我希望得到这个结果

 id , voteup , votedown
  2 ,  6     , 1

thanks.

Here is sqlfiddle Demo

这是sqlfiddle演示

I have tried to change the order of the columns like that

我试图改变那样的列的顺序

  ORDER BY cm.votedown asc ,cm.voteup DESC limit 1

But this also gives me wrong result if i have values like that

但如果我有这样的价值观,这也会给我错误的结果

   id ,voteup , votedown

   1  , 6     ,  0
   2  , 9     , 2 
   3  ,  4     , 9 
   4  ,  0     , 6

which gives

 1,  6 , 0

But i want

但我想要

2 , 9 , 2    > which is max (9-2)

fiddle for this last try

最后一次尝试的小提琴

1 个解决方案

#1

You have to place field maxe DESC first, followed by cm.votedown asc first in the ORDER BY clause:

您必须首先放置字段maxe DESC,然后在ORDER BY子句中首先放置cm.votedown asc:

SELECT cm.id ,cm.voteup,cm.votedown  FROM Table1 cm  
INNER JOIN ( 
   select id, MAX(voteup - votedown ) as maxe 
   from Table1 
   where voteup - votedown >= 5  
   group by id  
) tt on cm.id = tt.id
ORDER BY maxe DESC, cm.votedown asc, cm.voteup DESC, cm.id  limit 1

This query will pick the record having the biggest voteup - votedown difference. In case of ties, the query returns the record having the smallest votedown value.

此查询将选择具有最大投票权的记录 - 投票差异。在绑定的情况下,查询返回具有最小的投票值的记录。

Demo here

#1