sql语句简单面试题.doc

sql语句简单面试题 篇一：SQL语句面试题 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(#39;2005-05-09#39;,#39;胜#39;) insert into #tmp values(#39;2005-05-09#39;,#39;胜#39;) insert into #tmp values(#39;2005-05-09#39;,#39;负#39;) insert into #tmp values(#39;2005-05-09#39;,#39;负#39;) insert into #tmp values(#39;2005-05-10#39;,#39;胜#39;) insert into #tmp values(#39;2005-05-10#39;,#39;负#39;) insert into #tmp values(#39;2005-05-10#39;,#39;负#39;) 1)select rq, sum(case when shengfu=#39;胜#39; then 1 else 0 end)#39;胜#39;,sum(case when shengfu=#39;负#39; then 1 else 0 end)#39;负#39; from #tmp group by rq 2) select N.rq,N.勝,M.負 from ( select rq,勝=count(*) from #tmp where shengfu=#39;胜#39;group by rq)N inner join (select rq,負=count(*) from #tmp where shengfu=#39;负#39;group by rq)M on N.rq=M.rq 3)select a.rq,a.a1 胜,b.b1 负 from (select rq,count(rq) a1 from #tmp where shengfu=#39;胜#39; group by rq) a, (select rq,count(rq) b1 from #tmp where shengfu=#39;负#39; group by rq) b where a.rq=b.rq 2.请教一个面试中遇到的SQL语句的查询问题 表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。 ------------------------------------------ create table #tmp(A int,B int,C int) insert into #tmp values(#39;10#39;,#39;20#39;,#39;30#39;) --insert into #tmp values(#39;10#39;,#39;30#39;,#39;20#39;) --insert into #tmp values(#39;40#39;,#39;10#39;,#39;20#39;) select * from #tmp select (case when agt;b then a else b end),(case when bgt;c then b else c end ) from #tmp 3.面试题：一个日期判断的sql语句？ 请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间) ------------------------------------------ select * from #tmp where datediff(dd,rq,getdate())=0 select * from #tmp where rq=rtrim(convert(varchar,getdate(),23)) 4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，