Mysql 查询多条顺序(语句)

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) 和 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      |
+---------+-------------+------------+-------------+------------+