以前做过一道题目,一直没有来得及总结下来。贴图:

一道关于比赛胜负的Sql查询题目-LMLPHP

记得以前曾经找到了两种方法,今天试了一下,还是可以的,贴出过程:

一道关于比赛胜负的Sql查询题目-LMLPHP

下面是具体的查询方法:

原来放的是图片,今天又练习了一下,附代码:

 create TABLE #Match
(
matchDate datetime,
matchResult nvarchar()
) insert #Match select '2014-09-01','lost'
select '2014-09-19','win'
union all
select '2014-09-19','win'
union all
select '2014-09-19','lost'
union all
select '2014-09-01','win'
union all
select '2014-09-19','lost'
union all
select '2014-09-01','lost' select * from #Match --方法1
select m.matchDate,
sum(case when m.matchResult='win' then else end) win,
sum(case when m.matchResult='lost' then else end) lost
from #Match m group by m.matchDate --方法2
select m.matchDate,
sum(case m.matchResult when 'win' then else end) win,
sum(case m.matchResult when 'lost' then else end) lost
from #Match m group by m.matchDate
05-11 14:06