Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

sql overµÄ×÷Óü°Ó÷¨


RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
¿ÉʵÏÖ°´Ö¸¶¨µÄ×ֶηÖ×éÅÅÐò£¬¶ÔÓÚÏàͬ·Ö×é×ֶεĽá¹û¼¯½øÐÐÅÅÐò,
ÆäÖÐPARTITION BY Ϊ·Ö×é×ֶΣ¬ORDER BY Ö¸¶¨ÅÅÐò×Ö¶Î
over²»Äܵ¥¶ÀʹÓã¬ÒªºÍ·ÖÎöº¯Êý£ºrank(),dense_rank(),row_number()µÈÒ»ÆðʹÓá£
Æä²ÎÊý£ºover£¨partition by columnname1 order by columnname2£©
º¬Ò壺°´columname1Ö¸¶¨µÄ×ֶνøÐзÖ×éÅÅÐò£¬»òÕß˵°´×Ö¶Îcolumnname1µÄÖµ½øÐзÖ×éÅÅÐò¡£
ÀýÈ磺employees±íÖУ¬ÓÐÁ½¸ö²¿ÃŵļǼ£ºdepartment_id £½10ºÍ20
select department_id£¬rank£¨£© over£¨partition by department_id order by salary) from employees¾ÍÊÇÖ¸ÔÚ²¿ÃÅ10ÖнøÐÐнˮµÄÅÅÃû£¬ÔÚ²¿ÃÅ20ÖнøÐÐнˮÅÅÃû¡£Èç¹ûÊÇpartition by org_id£¬ÔòÊÇÔÚÕû¸ö¹«Ë¾ÄÚ½øÐÐÅÅÃû¡£
ÒÔÏÂÊǸöÈ˼û½â£º
sqlÖеÄoverº¯ÊýºÍrow_numbert()º¯ÊýÅäºÏʹÓ㬿ÉÉú³ÉÐкš£¿É¶ÔijһÁеÄÖµ½øÐÐÅÅÐò£¬¶ÔÓÚÏàֵͬµÄÊý¾ÝÐнøÐзÖ×éÅÅÐò¡£ÈçÏÂ±í£º
Ö´ÐÐÓï¾ä£ºselect row_number() over(order by AID DESC) as rowid,* from bbºóµÄ½á¹ûÈçÏ£º
rowid±êʶÐкÅÓÐÁË£¬Í¬Ê±AIDÒ²°´½µÐòÅÅÁС£AIDÓÐÖØ¸´µÄ¼Ç¼£¬Èç¹ûҪɾ³ýrowidΪ2Ëù¶ÔÓ¦µÄ¼Ç¼Ôò¿ÉÒÔ£º
with [a] as
(select row_number() over(order by AID desc) as rowid,* from bb)
delete from [a] where rowid=2
Èç¹û²é¿´rowid Ϊ5Ëù¶ÔÓ¦µÄ¼Ç¼µÄÐÅÏ¢£¬¿ÉÒÔ£º
with [b] as
(select row_number() over(order by AID desc) as rowid,* from bb)
select * from [b] where rowid=5
×¢Ò⣺
overÀïµÄorderÖ»Äܲé²éѯÀïµÄԭʼÊý¾Ý½øÐвÙ×÷£¬²»»á¶Ô¼ÆËã³öµÄÐÂÖµ»òÐÂ×Ö¶ÎÆð×÷Óá£
msdnÖеÄ˵·¨ÈçÏ£º
<ORDER BY ×Ó¾ä> Ö»ÄÜÒýÓÃͨ¹ý from ×Ó¾ä¿ÉÓõÄÁС£<ORDER BY ×Ó¾ä>²»ÄÜÓë¾ÛºÏ´°¿Úº¯ÊýÒ»ÆðʹÓá£


Ïà¹ØÎĵµ£º

SQL Serverµ¼³ö±íµ½EXCELÎļþµÄ´æ´¢¹ý³Ì


SQL Serverµ¼³ö±íµ½EXCELÎļþµÄ´æ´¢¹ý³Ì
 
·¢²¼Ê±¼ä£º2008.07.11 09:00     À´Ô´£ºÈüµÏÍø    ×÷ÕߣºÐ¡ÇÇ
¡¾ÈüµÏÍø£­IT¼¼Êõ±¨µÀ¡¿SQL Serverµ¼³ö±íµ½EXCELÎļþµÄ´æ´¢¹ý³Ì:
*--Êý¾Ýµ¼³öEXCEL
 
µ¼³ö±íÖеÄÊý¾Ýµ½Excel,°üº¬×Ö¶ÎÃû,ÎļþÎªÕæÕýµÄExcelÎļþ
,Èç¹ ......

Sql Server ÈÕÆÚ¸ñʽ»¯

Sql Server ÖÐÒ»¸ö·Ç³£Ç¿´óµÄÈÕÆÚ¸ñʽ»¯º¯Êý
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GE ......

CÓïÑÔÓëSQL SERVERÊý¾Ý¿â

1.ʹÓÃCÓïÑÔÀ´²Ù×÷SQL SERVERÊý¾Ý¿â,²ÉÓÃODBC¿ª·ÅʽÊý¾Ý¿âÁ¬½Ó½øÐÐÊý¾ÝµÄÌí¼Ó,ÐÞ¸Ä,ɾ³ý,²éѯµÈ²Ù×÷¡£
step1:Æô¶¯SQLSERVER·þÎñ,ÀýÈç:HNHJ,¿ªÊ¼²Ëµ¥ ->ÔËÐÐ ->net start mssqlserver
step2:´ò¿ªÆóÒµ¹ÜÀíÆ÷,½¨Á¢Êý¾Ý¿âtest,ÔÚtest¿âÖн¨Á¢test±í(a varchar(200),b varchar(200))
step3:½¨Á¢ÏµÍ³DSN,¿ªÊ¼²Ëµ ......

Ò»¸ö×Ö¶ÎÆ¥ÅäµÄsqlÓï¾äÊéд

Õ⼸Ì츺ÔðÒ»¸ö¼Ò½ÌÃÅ»§ÍøÕ¾µÄ¿ª·¢£¬»ùÓÚcakephp¿ò¼Ü¡£ÔÚÅàѵ»ú¹¹±í£¨schools£©ÖдæÔÚÒ»¸ö×Ö¶ÎsubjectÓÃÀ´´æ´¢ÁíÒ»¸öÊý¾Ý±í
£¨subjects£©ÖмǼµÄidÖµ£¬ÇÒ´æ´¢ÐÎʽΪ£º'1,2,3,4,5'¡£µ«ÊÇÔÚÓ¦Óø߼¶ËÑË÷¹ýÂËÊ±Ò³ÃæselectÑ¡ÏîoptionµÄ´«ÖµÎª
subjectsµÄidÖµ£¬ÐèÒªÅжϲéѯ±íschoolsÖÐsubject×ֶδæÔÚ´Ëid£¬¼´²éѯÏÔʾ´Ë¼Ç¼ ......

¾­µäSQLÓï¾ä´óÈ«

ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ