查询结果除以另一个查询结果的方法

Question

我能运行

SELECT killedby, COUNT(killedby) killcount FROM games_playerinfo WHERE killedby != '' GROUP BY killedby

获取数据。

killedby    killcount
Player1         1
Player2         1

我可以运行。

SELECT ign, COUNT(ign) numberofgames FROM games_playerinfo GROUP by ign

并获取

ign numberofgames
player1 7
player2 3
player3 3
player4 3

我希望将第一个查询结果中每个用户的结果都除以第二个查询结果。

因此结果应该如下：

ign      avgkillcount
player1  0.1429
player2  0.333333

我希望可以只使用一条查询语句来限制结果。我尝试使用内连接，但我认为我并不了解连接的作用，也不清楚连接是否能够实现我想要的功能。

- user1862477

Answer 1

试试这个：

SELECT
  t1.ign,
  (t1.numberofgames / t2.killcount) AS avgkillcount
FROM
(
    SELECT 
      ign, 
      COUNT(ign) numberofgames 
    FROM games_playerinfo 
    GROUP by ign
) t1
INNER JOIN
(
    SELECT 
      killedby, 
      COUNT(killedby) killcount
    FROM games_playerinfo 
    WHERE killedby != ''
    GROUP BY killedby
) t2 ON t1.ign = t2.killedby;

SQL Fiddle演示

- Mahmoud Gamal

Answer 2

查询：

SELECT g1.killedby ign,
       COUNT(g1.killedby)/(SELECT COUNT(ign) numberofgames 
                                  FROM games_playerinfo 
                                  WHERE ign = g1.killedby
                                  GROUP by ign) avgkillcount
FROM games_playerinfo g1
WHERE g1.killedby != '' 
GROUP BY g1.killedby

- Justin