莫愁前路无知己,天下谁人不识君。。。。

经典sql笔试题之一----值得你看看

上一篇 / 下一篇  2017-07-04 19:34:29 / 个人分类:软件面试问题

表结构SHENGFU
表内容: 
日期(RQ)    胜负(SF)
2005-05-09   胜
2005-05-09   胜
2005-05-09   负
2005-05-09   负
2005-05-10   胜
2005-05-10   负
2005-05-10   负
现要求生成以下结果:
日期        胜  负
2005-05-09  2   2 
2005-05-10  1   2
如何写sql?
mysql与oracle的写法相同
写法1:
select con.RQ '日期',
       sum(case con.sta
             when 1 then
              con.cou
             else
              0
           end) "胜",
       sum(case con.sta
             when 0 then
              con.cou
             else
              0
           end) "负"
  from (select t.RQ, count(1) cou, 1 sta from SHENGFU t
  where t.SF = '胜' group by t.RQ
  union all
  select t.RQ, count(1) cou, 0 sta  from SHENGFU t
  where t.SF = '负' group by t.RQ)con group by RQ,
  order by RQ;
写法2(good way!good way!):
select RQ '日期', 
sum(case when sh='胜' then 1 else 0 end)'胜',
sum(case when sh='负' then 1 else 0 end)'负' 
from SHENGFU group by RQ;
写法3:
select N.RQ '日期',N.胜,M.负 from (
select RQ,sh,count(*) 胜 from SHENGFU where sh='胜'group by riqi)N inner join
(select RQ,sh,count(*) 负 from SHENGFU where sh='负'group by riqi)M on N.riqi=M.riqi;

将胜或负改为1或0的写法:
日期(RQ)    胜负(SF)
2005-05-09   1
2005-05-09   1
2005-05-09   0
2005-05-09   0
2005-05-10   1
2005-05-10   0
2005-05-10   0
方法一:
select con.RQ '日期',
       sum(case con.SF
             when 1 then con.cou else 0 end) "胜",
       sum(case con.SF
             when 0 then con.cou else 0 end) "负"
from
(select t.RQ, t.SF, count(1) cou from SHENGFU t group by t.RQ, t.SF) con group by con.RQ,order by RQ;
方法二:
select RQ '日期', 
sum(case when sh=1 then 1 else 0 end)'胜',
sum(case when sh=0 then 1 else 0 end)'负' 
from SHENGFU group by RQ;
方法三:
写法3:
select N.RQ '日期',N.胜,M.负 from (
select RQ,sh,count(*) 胜 from SHENGFU where sh=1 group by riqi)N inner join
(select RQ,sh,count(*) 负 from SHENGFU where sh=0 group by riqi)M on N.riqi=M.riqi;

TAG: SQL sql 笔试

 

评分:0

我来说两句

Open Toolbar