莫愁前路无知己,天下谁人不识君。。。。
经典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
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
笔试