SQL语句简单面试题.doc

《SQL语句简单面试题.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、1.Ò»µÀsqlÓï¾äÃæÊÔÌ⣬¹ØÓÚgroupby±íÄÚÈÝ£º2005-05-09ʤ2005-05-09ʤ2005-05-09¸º2005-05-09¸º2005-05-10ʤ2005-05-10¸º2005-05-10¸ºÈç¹ûÒªÉú³ÉÏÂÁнá¹û,¸ÃÈçºÎдsqlÓï¾ä?ʤ¸º2005-05-09222005-05-1012------------------------------------------createtable#tmp(rqvarchar(10),shengfunchar(1))insertinto

2、#tmpvalues(2005-05-09,ʤ)insertinto#tmpvalues(2005-05-09,ʤ)insertinto#tmpvalues(2005-05-09,¸º)insertinto#tmpvalues(2005-05-09,¸º)insertinto#tmpvalues(2005-05-10,ʤ)insertinto#tmpvalues(2005-05-10,¸º)insertinto#tmpvalues(2005-05-10,¸º)1)selectrq,sum(casewhenshengfu=ʤthen1else0end)ʤ,

3、sum(casewhenshengfu=¸ºthen1else0end)¸ºfrom#tmpgroupbyrq2)selectn.rq,n.„Ù,m.Ø“from(selectrq,„Ù=count(*)from#tmpwhereshengfu=ʤgroupbyrq)ninnerjoin(selectrq,Ø“=count(*)from#tmpwhereshengfu=¸ºgroupbyrq)monn.rq=m.rq3)selecta.rq,a.a1ʤ,b.b1¸ºfrom(selectrq,count(rq)a1from#tmpwhereshengfu=ʤ

4、groupbyrq)a,(selectrq,count(rq)b1from#tmpwhereshengfu=¸ºgroupbyrq)bwherea.rq=b.rq2.Çë½ÌÒ»¸öÃæÊÔÖÐÓöµ½µÄsqlÓï¾äµÄ²éѯÎÊÌâ±íÖÐÓÐabcÈýÁÐ,ÓÃsqlÓï¾äʵÏÖ£ºµ±aÁдóÓÚbÁÐʱѡÔñaÁзñÔòÑ¡ÔñbÁУ¬µ±bÁдóÓÚcÁÐʱѡÔñbÁзñÔòÑ¡ÔñcÁС£------------------------------------------createtable#tmp(aint,bint,

5、cint)insertinto#tmpvalues(10,20,30)--insertinto#tmpvalues(10,30,20)--insertinto#tmpvalues(40,10,20)select*from#tmpselect(casewhena>bthenaelsebend),(casewhenb>cthenbelsecend)from#tmp3.ÃæÊÔÌ⣺һ¸öÈÕÆÚÅжϵÄsqlÓï¾ä£¿ÇëÈ¡³ötb_send±íÖÐÈÕÆÚ(sendtime×Ö¶Î)Ϊµ±ÌìµÄËùÓмǼ?(sendtime×Ö¶Î

6、ΪdatetimeÐÍ£¬°üº¬ÈÕÆÚÓëʱ¼ä)------------------------------------------select*from#tmpwheredatediff(dd,rq,getdate())=0select*from#tmpwhererq=rtrim(convert(varchar,getdate(),23))1.ÓÐÒ»ÕÅ±í£¬ÀïÃæÓÐ3¸ö×ֶΣºÓïÎÄ£¬Êýѧ£¬Ó¢Óï¡£ÆäÖÐÓÐ3Ìõ¼Ç¼·Ö±ð±íʾÓïÎÄ70·Ö£¬Êýѧ80·Ö£¬Ó¢Óï58·Ö£¬ÇëÓÃÒ»ÌõsqlÓ

7、ï¾ä²éѯ³öÕâÈýÌõ¼Ç¼²¢°´ÒÔÏÂÌõ¼þÏÔʾ³öÀ´£¨²¢Ð´³öÄúµÄ˼·£©£º´óÓÚ»òµÈÓÚ80±íʾÓÅÐ㣬´óÓÚ»òµÈÓÚ60±íʾ¼°¸ñ£¬Ð¡ÓÚ60·Ö±íʾ²»¼°¸ñ¡£ÏÔʾ¸ñʽ£ºÓïÎÄÊýѧӢÓï¼°¸ñÓÅÐã²»¼°¸ñ------------------------------------------createtable#tmp(ÓïÎÄint,Êýѧint,Ó¢Óïint)insertinto#tmpvalues(70,80,58)--insertinto#t

8、mpval