- 1.一道SQL语句面试题,关于group by
- 表内容:
- 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 #tmp(rq varchar(10),shengfu nchar(1))
- insert into #tmp values('2005-05-09','胜')
- insert into #tmp values('2005-05-09','胜')
- insert into #tmp values('2005-05-09','负')
- insert into #tmp values('2005-05-09','负')
- insert into #tmp values('2005-05-10','胜')
- insert into #tmp values('2005-05-10','负')
- insert into #tmp values('2005-05-10','负')
- 1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
- 2) select N.rq,N.勝,M.負 from (
- select rq,勝=count(*) from #tmp where shengfu='胜'group by rq)N inner join
- (select rq,負=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
- 3)select a.col001,a.a1 胜,b.b1 负 from
- (select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
- (select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
- where a.col001=b.col001
SQL语句group by
最新推荐文章于 2023-03-01 10:24:15 发布