Mysql:如何计算两个平均值之间的差异？

Question

我有一张 table :

+------------+--------------+-------+------------+
| Name       | Nation       | Score | Game_date  |
+------------+--------------+-------+------------+
| Ginobili   | Argentina    |    48 | 2005-01-21 |
| Irving     | Australia    |    44 | 2014-04-06 |
| Kirilenko  | Soviet Union |    31 | 2006-11-11 |
| Kobe       | USA          |    81 | 2006-01-22 |
| LeBron     | USA          |    52 | 2014-12-06 |
| Mutombo    | Congo        |    39 | 1992-02-03 |
| Nowitzki   | Germany      |    48 | 2011-05-18 |
| PauGasol   | Spain        |    46 | 2015-01-11 |
| SteveNash  | Canada       |    42 | 2006-12-08 |
| TonyParker | France       |    55 | 2008-11-06 |
| YaoMing    | China        |    41 | 2009-02-23 |
| YiJianlina | China        |    31 | 2010-03-27 |
+------------+--------------+-------+------------+

我想计算美国的 avg(score) - 中国的 avg(score)。

我试过了

select avg(score) from nba where nation = "USA" - 
avg(score) from nba where nation = "China";

但这是错误的!

Answer 1

您可以使用 AVG 和 CASE WHEN:

SELECT AVG(CASE WHEN nation = 'USA' THEN score END) -
       AVG(CASE WHEN nation = 'China' THEN score END) AS result
FROM nba
WHERE nation IN ('USA', 'China');

另一种选择是使用 UNION:

SELECT SUM(sub.r) AS result
FROM ( SELECT avg(score) AS r
       from nba 
       where nation = 'USA' 
       UNION ALL
       SELECT -avg(score) 
       from nba 
       where nation = 'China') AS sub