Mysql查询order by multiple items(语句)

Question

这就是我已经拥有的。

SELECT karma
     , profanity
     , username 
  FROM users 
 ORDER 
    BY (karma - profanity) DESC 
 LIMIT 10

如何按ORDER BY (karma - profanity) DESC LIMIT 10和ORDER BY profanity DESC LIMIT 10订购

CREATE TABLE Test
    (`id` int, `username` varchar(55), `karma` int,`profanity` int)
;

INSERT INTO Test
    (`id`, `username`, `karma`, `profanity`)
VALUES
    (1, 'User1', '10', '1'),
    (2, 'User2', '8', '2'),
    (3, 'User3', '1', '2'),
    (4, 'User4', '11', '1'),
    (5, 'User5', '5', '0'),
    (6, 'User6', '6', '3'),
    (7, 'User7', '1', '1'),
    (8, 'User8', '2', '3'),
    (9, 'User9', '2', '1'),
    (10, 'User10', '1', '7'),
    (11, 'User11', '7', '7'),
    (12, 'User12', '1', '1'),
    (13, 'User13', '10', '0'),
    (14, 'User14', '1', '3'),
    (15, 'User15', '7', '0')
;

期望的结果将如下所示：

karma, profanity, username | profanity  username
   10          0  User13             7  User11
   11          1  User4              7  User10
   10          1  User1              3  User8
    7          0  User15             3  User6
    8          2  User2              3  User14
    5          0  User5              2  User3
    6          3  User6              2  User2
    2          1  User9              1  User4
    1          1  User7              1  User7
    1          1  User12             1  User9

两者都有一个输出result[i].(karma - profanity) and result[i].profanity)

这两种排序合并为一个具有不同顺序的标注

http://sqlfiddle.com/#!9/7ca828/2

Answer 1

我认为这是一个显示问题--你通常会在应用程序级别代码中解决的那种问题，但不管怎样…

SELECT a.karma a_karma
     , a.profanity a_profanity
     , a.username a_username
     , b.profanity b_profanity
     , b.username b_username
  FROM
     ( SELECT *,@kp:=@kp+1 kp FROM test, (SELECT @kp:=0) vars ORDER BY karma-profanity DESC LIMIT 10 ) a
  JOIN
     ( SELECT *,@p:=@p+1 p FROM test, (SELECT @p:=0) vars ORDER BY profanity DESC LIMIT 10 ) b
    ON b.p = a.kp;
+---------+-------------+------------+-------------+------------+
| a_karma | a_profanity | a_username | b_profanity | b_username |
+---------+-------------+------------+-------------+------------+
|      10 |           0 | User13     |           7 | User11     |
|      11 |           1 | User4      |           7 | User10     |
|      10 |           1 | User1      |           3 | User8      |
|       7 |           0 | User15     |           3 | User6      |
|       8 |           2 | User2      |           3 | User14     |
|       5 |           0 | User5      |           2 | User3      |
|       6 |           3 | User6      |           2 | User2      |
|       2 |           1 | User9      |           1 | User4      |
|       1 |           1 | User7      |           1 | User7      |
|       1 |           1 | User12     |           1 | User9      |
+---------+-------------+------------+-------------+------------+