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('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
2.Çë½ÌÒ»¸öÃæÊÔÖÐÓöµ½µÄSQLÓï¾äµÄ²éѯÎÊÌâ
±íÖÐÓÐA B CÈýÁÐ,ÓÃSQLÓï¾äʵÏÖ£ºµ±AÁдóÓÚBÁÐʱѡÔñAÁзñÔòÑ¡ÔñBÁУ¬µ±BÁдóÓÚCÁÐʱѡÔñBÁзñÔòÑ¡ÔñCÁС£
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.ÃæÊÔÌ⣺һ¸öÈÕÆÚÅжϵÄsqlÓï¾ä£¿
ÇëÈ¡³ötb_send±íÖÐÈÕÆÚ(SendTime×Ö¶Î)Ϊµ±ÌìµÄËùÓмǼ?(SendTime×Ö¶ÎΪdatetimeÐÍ£¬°üº¬ÈÕÆÚÓëʱ¼ä)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.ÓÐÒ»ÕÅ±í£¬ÀïÃæÓÐ3¸ö×ֶΣºÓïÎÄ£¬Êýѧ£¬Ó¢Óï¡£ÆäÖÐÓÐ3Ìõ¼Ç¼·Ö±ð±íʾÓïÎÄ70·Ö£¬Êýѧ80·Ö£¬Ó¢Óï58·Ö£¬ÇëÓÃÒ»ÌõsqlÓï¾ä²éѯ³öÕâÈýÌõ¼Ç¼²¢°´ÒÔÏÂÌõ¼þÏÔʾ³öÀ´£¨²¢Ð´³öÄúµÄ˼·£©
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
½ñÌìÔÚһ̨Ðé»úÉÏ°²×° sql server 2005£¬¾ÓÈ»±¨1´íÁË£¨ÎÞÓïÁË£¬×°Õâ¸ö¶«Î÷¶¼Åª²»ºÃ%>_<%£©¿ªÊ¼ÒÔΪÊÇÒòΪADÓòµÄÔÒò£¬ºóÀ´É¾µôÓòÓÖÖؽ¨Óò£¬È»ºóÔÙ×°£¬ÓÖ±¨2´í£¬ÓÚÊÇÎʱðÈËËÑË÷µÈµÈ£¬ÕÒµ½ÔÒò˵ҪÖØÐÂ×¢²áinstaller£¬×îÖÕ°²×°³É¹¦ÁË£¬º¦ÎÒ¼Ó°àŪµÄ£¬¸ßÐ˵ÄÊÇ°²×°ºÃÀ²£¬¿ÉÊÇÎÒûÓÐŪÃ÷°×µ½µ×ÊÇɶÔÒòµ¼ÖÂsql°²×°²»ÁËÄØ£ ......
ORACLEPL/SQL»ù´¡--ÓαêµÄѧϰÀ´Ô´£º
Óαê×ÖÃæÀí½â¾ÍÊÇÓζ¯µÄ¹â±ê¡£
ÓÃÊý¾Ý¿âÓïÑÔÀ´ÃèÊö£ºÓαêÊÇÓ³ÉäÔÚ½á¹û¼¯ÖÐÒ»ÐÐÊý¾ÝÉϵÄλÖÃʵÌ壬ÓÐÁËÓαêÓû§¾Í¿ÉÒÔ·ÃÎʽá¹û¼¯ÖеÄÈÎÒâÒ»ÐÐÊý¾ÝÁË£¬½«Óαê·ÅÖõ½Ä³Ðк󣬼´¿É¶Ô¸ÃÐÐÊý¾Ý½øÐвÙ×÷£¬ÀýÈçÌáÈ¡µ±Ç°ÐеÄÊý¾ÝµÈµÈ¡£
......
OracleµÄsql*plusÊÇÓëoracle½øÐн»»¥µÄ¿Í»§¶Ë¹¤¾ß¡£ÔÚsql*plusÖУ¬¿ÉÒÔÔËÐÐsql*plusÃüÁîÓësql*plusÓï¾ä¡£
¡¡¡¡
¡¡¡¡ÎÒÃÇͨ³£Ëù˵µÄDML¡¢DDL¡¢DCLÓï¾ä¶¼ÊÇsql*plusÓï¾ä£¬ËüÃÇÖ´ÐÐÍêºó£¬¶¼¿ÉÒÔ±£´æÔÚÒ»¸ö±»³ÆΪsql bufferµÄÄÚ´æÇøÓòÖУ¬²¢ÇÒÖ»Äܱ£´æÒ»Ìõ×î½üÖ´ÐеÄsqlÓï¾ä£¬ÎÒÃÇ¿ÉÒÔ¶Ô±£´æÔÚsql bufferÖеÄsql Óï¾ä½ø ......