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

Question

我有一个以下格式的足球赛程表。

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

请有人能给我一些帮助。

Answer 1

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

> 的 bool 结果, <和 =将自动转换为整数 0或 1 , 通过 mysql，然后可以总结。