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
建表语句:
CASE WHEN 方法:
PIVOT 实现:
该方法在版本较高的SQLServer 和 Oracle 11G以后的版本可用
表内容:
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
建表语句:
点击(此处)折叠或打开
- create table test_a (rq varchar(10), sf varchar(4));
-
- insert into test_a values(\'2005-05-09\',\'胜\');
- insert into test_a values(\'2005-05-09\',\'胜\');
- insert into test_a values(\'2005-05-09\',\'负\');
- insert into test_a values(\'2005-05-09\',\'负\');
- insert into test_a values(\'2005-05-10\',\'胜\');
- insert into test_a values(\'2005-05-10\',\'负\');
- insert into test_a values(\'2005-05-10\',\'负\');
- commit;
点击(此处)折叠或打开
- select rq,
- sum((case sf
- when \'胜\' then
- 1
- else
- 0
- end)) as 胜,
- sum((case sf
- when \'负\' then
- 1
- else
- 0
- end)) as 负
- from test_a
- group by rq;
点击(此处)折叠或打开
- select * from test_a pivot(count(sf) for sf in(\'胜\', \'负\')) order by rq;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29507357/viewspace-1202963/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29507357/viewspace-1202963/