mysql - MySQL中如何统计多列的出现次数，并通过统计列的计算对结果进行排序？

Question

我正在尝试计算多列中的出现次数，然后输出结果并根据计算对它们进行排序。

计算 killer_uid 出现了多少次。然后统计他们的ID在victim_uid中出现了多少次。然后根据killer_uid/victim_uid的个数输出结果。请参阅下面的示例表和所需的输出。

Table = kill_feed
+------------+------------+
| killer_uid | victim_uid |
+------------+------------+
|          1 |          2 |
|          2 |          3 |
|          2 |          3 |
|          2 |          1 |
|          2 |          1 |
|          1 |          1 |
+------------+------------+

期望的输出:killer_uid 作为 player_id，计算 killer_uid 为 player_kills 和 victim_uid 为 player_deaths 的实例，然后除以 player_kills/player_deaths(如果除以 0 则返回 0)并按这些结果 DESC 排序。

+-----------+--------------+---------------+
| player_id | player_kills | player_deaths |
+-----------+--------------+---------------+
|         2 |            4 |             1 | 
|         1 |            2 |             3 | 
|         3 |            0 |             2 | 
+-----------+--------------+---------------+

Order by player_kills / player_deaths
1st = 2 since 4/1 = 4
2nd = 1 since 2/3 = 0.66
3rd = 3 since 0/2 = 0 

我尝试使用但抛出语法错误的语句。

SELECT killer_uid as player_id, 
       Count(killer_uid) AS player_kills, 
       Count(victim_uid) AS player_deaths 
FROM   kill_feed
GROUP  BY player_id 
ORDER  BY player_kills / player_deaths DESC 

Answer 1

例如:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,killer_uid INT NOT NULL
,victim_uid INT NOT NULL
);

INSERT INTO my_table (killer_uid,victim_uid) VALUES
(1,2),
(2,3),
(2,3),
(2,1),
(2,1),
(1,1);


SELECT player_id
     , SUM(x) player_kills
     , SUM(y) player_deaths
  FROM
     ( SELECT killer_uid player_id
            , 1 x
            , 0 y
         FROM my_table
        UNION ALL
       SELECT victim_uid
            , 0
            , 1
         FROM my_table
     ) x
 GROUP
    BY player_id
 ORDER
    BY COALESCE(SUM(x)/SUM(y),SUM(x)) DESC;
+-----------+--------------+---------------+
| player_id | player_kills | player_deaths |
+-----------+--------------+---------------+
|         2 |            4 |             1 |
|         1 |            2 |             3 |
|         3 |            0 |             2 |
+-----------+--------------+---------------+