SQLɾ³ýÖظ´Êý¾Ý
Ò»¡¢¾ßÓÐÖ÷¼üµÄÇé¿ö
I.¾ßÓÐΨһÐÔµÄ×Ö¶Îid(ΪΨһÖ÷¼ü)
delete Óû§±í
where id not in
(
select max(id) from Óû§±í group by col1,col2,col3...
)
group by ×Ó¾äºó¸úµÄ×ֶξÍÊÇÄãÓÃÀ´ÅжÏÖظ´µÄÌõ¼þ£¬ÈçÖ»ÓÐcol1£¬
ÄÇôֻҪcol1×Ö¶ÎÄÚÈÝÏàͬ¼´±íʾ¼Ç¼Ïàͬ¡£
II.¾ßÓÐÁªºÏÖ÷¼ü
¼ÙÉècol1+','+col2+','...col5 ΪÁªºÏÖ÷¼ü
£¨ÕÒ³öÏàͬ¼Ç¼£©
select * from Óû§±í where col1+','+col2+','...col5 in
(
select max(col1+','+col2+','...col5) from Óû§±í
group by col1,col2,col3,col4
having count(*)>1
)
group by ×Ó¾äºó¸úµÄ×ֶξÍÊÇÄãÓÃÀ´ÅжÏÖظ´µÄÌõ¼þ£¬
ÈçÖ»ÓÐcol1£¬ÄÇôֻҪcol1×Ö¶ÎÄÚÈÝÏàͬ¼´±íʾ¼Ç¼Ïàͬ¡£
»òÕߣº
£¨ÕÒ³öÏàͬ¼Ç¼£©
select * from Óû§±í where exists (select 1 from Óû§±í x where Óû§±í.col1 = x.col1 and
Óû§±í.col2= x.col2 group by x.col1,x.col2 having count(*) >1)
III:ÅжÏËùÓеÄ×Ö¶Î
select * into #aa from Óû§±í group by id1,id2,....
delete Óû§±í
insert into Óû§±í select * from #aa
¶þ¡¢Ã»ÓÐÖ÷¼üµÄÇé¿ö
I.ÓÃÁÙʱ±íʵÏÖ
select identity(int,1,1) as id,* into #temp from Óû§±í
delete #temp
where id not in
(
select max(id) from # group by col1,col2,col3...
)
delete Óû§±í ta
inset into ta(...) select ..... from #temp
II.Óøıä±í½á¹¹£¨¼ÓÒ»¸öΨһ×ֶΣ©À´ÊµÏÖ
alter Óû§±í add newfield int identity(1,1)
delete Óû§±í
where newfield not in
(
select min(newfield) from Óû§±í group by ³ýnewfieldÍâµÄËùÓÐ×Ö¶Î
)
alter Óû§±í drop column newfield
Ïà¹ØÎĵµ£º
Ò»¡¢SQL SERVER ºÍACCESSµÄÊý¾Ýµ¼Èëµ¼³ö
³£¹æµÄÊý¾Ýµ¼Èëµ¼³ö£º
ʹÓÃDTSÏòµ¼Ç¨ÒÆÄãµÄAccessÊý¾Ýµ½SQL Server£¬Äã¿ÉÒÔʹÓÃÕâЩ²½Öè:
¡¡¡¡¡ð1ÔÚSQL SERVERÆóÒµ¹ÜÀíÆ÷ÖеÄTools£¨¹¤¾ß£©²Ëµ¥ÉÏ£¬Ñ¡ÔñData Transformation
¡¡¡¡¡ð2Services£¨Êý¾Ýת»»·þÎñ£©£¬È»ºóÑ¡Ôñ czdImport Data£¨µ¼ÈëÊý¾Ý£©¡£
¡¡¡¡¡ ......
SQL SERVER ·þÎñ
SQL Server Analysis Services £ºÎªÉÌÒµÖÇÄÜÓ¦ÓóÌÐòÌṩÁª»ú·ÖÎö´¦Àí(OLAP)ºÍÊý¾ÝÍÚ¾ò¹¦ÄÜ(ºËÐÄ·þÎñ)¡£
SQL Server Browser£º½« SQL Server Á¬½ÓÐÅÏ¢Ìṩ¸ø¿Í»§¶Ë¼ÆËã»ú
SQL Server FullText Search£º¿ìËÙ´´½¨½á¹¹»¯ºÍ°ë½á¹¹»¯Êý¾ÝµÄÄÚÈݺÍÊôÐÔ
SQL Server VSS Writer£ºÌṩͨ¹ý Windows VSS »ù´¡½á¹¹ ......
SELECT
count
(
userName
)
from
`userInfo`
GROUP
BY
userName
HAVING
count
(
userName
)
>
1¡£
Õâ¸öHavingÒ»¶¨Òª·ÅÔÚGROUP BYµÄºóÃæ¡£
¶øÇÒºÜÌØÊâµÄÇé¿öÊÇÔÚÓÐgroup byµÄʱºò£¬ÊDz»ÄÜÓõ½where×Ó¾äµÄ¡£¶øÇÒHaving×Ó¾äÒ»°ãÊÇ·ÅÔÚÆäËû×Ó¾äµÄºóÃæµÄ¡£
µ±ÎÒÃÇÑ¡²»Ö»Ò»¸öÀ¸Î»£¬ÇÒÆä ......
´ËÎÊÌâÕÛÌÚ°ëÌì,googleÒ²ËÑË÷ÁË,ÍøÉϺܶàÅóÓÑÒ»°ã×öµ½µÚ¶þ²½²»³É¹¦,ÎÒÒ²²îµã·ÅÆúÁË.×îºóͨ¹ýµÚÈý²½Á¬½Ó³É¹¦ÁË.
1.·À»ðǽÔÊÐí1433
2.ÅäÖù¤¾ßÀïµÄSQL Server ÅäÖùÜÀíÆ÷.´ò¿ªºó,¿ªÆôÓÒ±ßÈý¸öÐÒé.²¢¶Ôtcp/ipÈçÏÂÅäÖÃ
3.ÍøÉϴ󲿷Ö×öµ½µÚ¶þ°æÖØÆôsql server·þÎñ,ÈÔÈ»ÎÞЧ.Æäʵ,ÐèÒªÐÞ¸ÄÁ¬½Ó×Ö·û´®.Ö¸¶¨ÎÒÃÇÅäÖõĶ ......