sql经典面试题,胜负横向分组显示 - 数据库知识,sqlserver,mysql知识积累 - CSS模版园-免费网站模版下载
当前位置 CSS模版园 >> 网站制作 >> 数据库 >> sql经典面试题,胜负横向分组显示

sql经典面试题,胜负横向分组显示

sql面试题,胜负横向分组显示:

一表有如下数据:

如果要生成下列结果, 该如何写sql语句?

创建临时表,输入测试数据

--创建临时表,输入测试数据
create table #tmp(CreateDate varchar(10),Results nchar(1))
 
insert into #tmp values
    ('2005-05-09','胜'),
	('2005-05-09','胜'),
	('2005-05-09','负'),
	('2005-05-09','负'),
	('2005-05-10','胜'),
	('2005-05-10','负'),
	('2005-05-10','负')

解决方案

--写法1
select CreateDate, 
	sum(case when Results='胜' then 1 else 0 end) 胜,
	sum(case when Results='负' then 1 else 0 end) 负
from #tmp group by CreateDate
--写法2
select CreateDate, 
	(select count(0) from #tmp where Results='胜' and CreateDate=a.CreateDate) 胜,
	(select count(0) from #tmp where Results='负' and CreateDate=a.CreateDate) 负
from #tmp a group by CreateDate
--写法3
select a.CreateDate,a.胜,b.负 
from (select CreateDate,胜=count(*) from #tmp where Results='胜'group by CreateDate) a inner join
	(select CreateDate,负=count(*) from #tmp where Results='负'group by CreateDate) b on a.CreateDate=b.CreateDate

 

声明本站所有资源仅供学习与参考,请勿用于商业用途。转载请注明来自:cssteach.com

原文http://cssteach.com/html/show-14-135.html

若文章内容有错误或无法下载,请在回复中说明(可无需登录,直接评论)

最新文章

图文

热门网站模版