mysql - 从单个表返回多个计数

我有以下格式的足球装备表。

date date primary key
homescore int(4)
awayscore int(4)

数据以以下格式存储

DATE        |   HOMESCORE    |  AWAYSCORE
------------------------------------------
01-01-2014  |       1        |      0
08-01-2014  |       2        |      1
15-01-2014  |       1        |      1
22-01-2014  |       3        |      2
29-01-2014  |       0        |      0
06-02-2014  |       1        |      3

等等...

我想运行一个查询以返回获胜，丢失和抽奖的总数。

select count(*) as won from fixtures where homescore > awayscore;
select count(*) as lostfrom fixtures where homescore < awayscore;
select count(*) as drawnfrom fixtures where homescore = awayscore;

这个查询的结果看起来像是...

Won      Lost      Drawn
3        1         2

请有人能帮我一下。

最佳答案

SELECT SUM(homescore > awayscore) AS won,
   SUM(homescore < awayscore) AS lost,
   SUM(homescore = awayscore) AS tie
FROM ...

>，<和=的布尔结果将由mysql自动转换为整数0或1，然后可以将其求和。