一道经典的SQL面试题

还是公司同事出的面试题，题目：统计胜负结果

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下简单快捷，但是在第一种查询下使用结果不尽如人意。

一道经典的SQL面试题

原文：http://www.cnblogs.com/xdwy/p/4976452.html