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Óï¾ä²éѯ³öÕâÈýÌõ¼Ç¼²¢°´ÒÔÏÂÌõ¼þÏÔʾ³öÀ´£¨²¢Ð´³öÄúµÄ˼·£©
Ïà¹ØÎĵµ£º
update anketo1 set fb = substring('10',fb+1,1) WHERE id =5
ºóÀ´ÎÊÁËBM,BM˵ֱ½ÓÈ¡·´
update anketo1 set fb = ~ fb WHERE id =5
ÎÒ¸æËßÁËBMÓÃsubstringÒ²¿ÉÒÔ.
BM˵:"Õâ¼Ò»ïÀ÷º¦£¬ÕâÒ²ÄÜÏë³ö,ÕâÊôÓÚаÃÅÎ书."
×îºóÓÖÏëµ½ÓÃABSÈ¡¾ø¶ÔÖµÒ²¿ÉÒÔ
update anketo1 set fb = abs(fb-1)
ÎÒ¾õµÃ²»¹ÜÊÇʲôÎ书,ÄÜÖ ......
ÅäÖÃʹÓà SQL ServerÌṩ³ÌÐò
<configuration>
<system.web> <-- ¸ü¸ÄÌṩ³ÌÐòÅäÖ㺠-->
<membership defaultProvider="AspNetSqlProvider" />
</system.web>
<membership> <--ÅäÖÃÌṩ³ÌÐò -->
<prov ......
Ò»¡¢ÕâÊÇÕâ¸öϵÁеÄ×îºóÒ»½ÚÁË£¬×Ô¶¨Ò帴ÔÓÊý¾ÝÀàÐÍÄ¿Ç°Ö»ÄÜͨ¹ýCLRÀ´ÊµÏÖ¡£ÎªÁËÔÚ SQL Server ÖÐÔËÐУ¬ÄúµÄ UDT ±ØÐëʵÏÖ UDT ¶¨ÒåÖеÄÒÔÏÂÒªÇó£º
1.¸Ã UDT ±ØÐëÖ¸¶¨ Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute¡£System.SerializableAttribute ¿ÉÑ¡Ó㬵«½¨ÒéʹÓá£
2.UDT ±ØÐëͨ¹ý´´½¨¹«¹²µÄ static£¨M ......
½ñÌìÔÚһ̨Ðé»úÉÏ°²×° sql server 2005£¬¾ÓÈ»±¨1´íÁË£¨ÎÞÓïÁË£¬×°Õâ¸ö¶«Î÷¶¼Åª²»ºÃ%>_<%£©¿ªÊ¼ÒÔΪÊÇÒòΪADÓòµÄÔÒò£¬ºóÀ´É¾µôÓòÓÖÖؽ¨Óò£¬È»ºóÔÙ×°£¬ÓÖ±¨2´í£¬ÓÚÊÇÎʱðÈËËÑË÷µÈµÈ£¬ÕÒµ½ÔÒò˵ҪÖØÐÂ×¢²áinstaller£¬×îÖÕ°²×°³É¹¦ÁË£¬º¦ÎÒ¼Ó°àŪµÄ£¬¸ßÐ˵ÄÊÇ°²×°ºÃÀ²£¬¿ÉÊÇÎÒûÓÐŪÃ÷°×µ½µ×ÊÇɶÔÒòµ¼ÖÂsql°²×°²»ÁËÄØ£ ......
1. Èç¹ûÄãÏ£ÍûʹÓÃselcet topÓï¾ä£¬²¢ÇÒ»¹Òª¸½´øwhereÌõ¼þ£¬ÄÇôÌõ¼þÖеÄÁо͵ÃÊǺÏÊʵÄË÷Òý£¬Èç¾Û¼¯Ë÷Òý¡¢¸´ºÏË÷ÒýÀïµÄÖ÷ÁÐ
µÈ£¬Í¬Ê±£¬whereÌõ¼þÀïÒ²Òª¾¡Á¿±Ü¿ªÊ¹Óú¯Êý£¬or£¬ÅжÏNULLµÈ»áÒýÆðÈ«²¿É¨ÃèµÄÓï¾ä£¬²»È»Ö´ÐеÄÊÇÈ«±íɨÃè¡£
2. ͨ¹ýÉèÖÃSTATISTICSÎÒÃÇ¿ÉÒԲ鿴ִÐÐSQLʱµÄÖ´ÐÐЧÂÊÒÔ¼°Ïà¹ØÐÔÄܲâÊÔ ......