一道sql练习题
表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
1
2 DROP TABLE #Temp
3
4 CREATE TABLE #Temp
5 (
6 Date VARCHAR(36),
7 State CHAR(10)
8 )
9
10 INSERT INTO #Temp VALUES ('2005-05-09','胜')
11 insert into #Temp values('2005-05-09','胜')
12 insert into #Temp values('2005-05-09','负')
13 insert into #Temp values('2005-05-09','负')
14 insert into #Temp values('2005-05-10','胜')
15 insert into #Temp values('2005-05-10','负')
16 insert into #Temp values('2005-05-10','负')
17
18
19
20 select * from #Temp
21
22 /* 第一种方法 */
23 SELECT Date,SUM(CASE State WHEN '胜' THEN 1 ELSE 0 END ) AS '胜',
24 SUM(CASE State WHEN '负' THEN 1 ELSE 0 END ) AS '负'
25 FROM #Temp
26 GROUP BY Date
27
28
29 /* 第二种方法 */
30 SELECT A.Date,A.胜,B.负 FROM
31 (SELECT Date, '胜'=COUNT(*) FROM #Temp WHERE State='胜' GROUP BY Date) A INNER JOIN
32 (SELECT Date, '负'=COUNT(*) FROM #Temp WHERE State='负' GROUP BY Date) B ON A.Date=B.Date
33 ------------------------------------------------------------------------------------------
34
35
