还是公司同事出的面试题，题目：统计胜负结果
A表：
date result
2015-11-18 胜
2015-11-18 负
2015-11-18 胜
2015-11-17 胜
2015-11-17 负
查询出来的结果：
date 胜 负
2015-11-18 2 1
2015-11-17 1 1
正巧不久前去面一家公司也刚好考到这个题，查出来的结果还比这个难一点。
id date 胜 负
1 2015-11-18 2 1
2 2015-11-17 1 1
大体思路是差不多的，这里给出几种解决方法。
一、采用子查询
SELECT date,
(SELECT COUNT(*) FROM A WHERE date=outtable.date and result=`胜`) AS `胜`,
(SELECT COUNT(*) FROM A WHERE date=outtable.date and result=`负`) AS `负`
FROM A AS outtable
GROUP BY date
ORDER BY date DESC
二、子查询结果作为两个新表，使用where进行关联
SELECT a1.date AS date,a1.num AS '胜',a2.num AS '负' FROM
(SELECT date, count(*) AS num FROM A WHERE result='胜' GROUP BY date) AS a1,
(SELECT date, count(*) AS num FROM A WHERE result='负' GROUP BY date) AS a2
WHERE a1.date=a2.date
ORDER BY date DESC
三、子查询结果作为两个新表，使用join进行表关联
SELECT a1.date AS date,a1.num AS '胜',a2.num AS '负' FROM
(SELECT date, count(*) AS num FROM A WHERE result='胜' GROUP BY date) AS a1
INNER JOIN
(SELECT date, count(*) AS num FROM A WHERE result='负' GROUP BY date) AS a2
ON a1.date=a2.date
ORDER BY date DESC
如何在查询结果中加id呢？
在SQL Sever下可使用id=identity(int,1,1)，但在MySQL下这种方法似乎不行，那么我们采用下面这种方法：
SET @cout=0;
SELECT (@cout:=@cout+1) AS id,a1.date AS date,a1.num AS '胜',a2.num AS '负' FROM
(SELECT date, count(*) AS num FROM A WHERE result='胜' GROUP BY date ORDER BY date DESC) AS a1,
(SELECT date, count(*) AS num FROM A WHERE result='负' GROUP BY date ORDER BY date DESC) AS a2
WHERE a1.date=a2.date
ORDER BY date DESC;
这种方法在MySQL下简单快捷，但是在第一种查询下使用结果不尽如人意。