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

SQL Server 2005ÖеÄT SQLÔöÇ¿B

SQL2005ÖÐrow_number( )¡¢rank( )¡¢dense_rank( )¡¢ntile( )º¯ÊýµÄÓ÷¨(2) ÊÕ²Ø
   SQL server 2005ÐÂÔöµÄ¼¸¸öº¯Êý,·Ö±ðÊÇrow_number( )¡¢rank( )¡¢,DENSE_RANK( )¡¢ntile( )ÏÂÃæÒÔʵÀý·Ö±ð¼òµ¥½²½â¡£
1.row_number( )
         ÏÈÀ´µãÊý¾Ý,ÏȽ¨¸ö±í
SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')
Ö±½ÓÓÃÀý×Ó˵Ã÷ÎÊÌâ:
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
FirstName,
Age
from Person
³öÏÖµÄÊý¾ÝÈçÏÂ
Row Number by Age                FirstName            Age
--------------------------                 ----------            --------
1                                                Larry                   5
2                                    


Ïà¹ØÎĵµ£º

º½¿Õ¹«Ë¾¹ÜÀíϵͳ(VC++ ÓëSQL 2005)

ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
      ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......

sql Exists in

 Ê×ÏÈ£¬ÎÒÃÇ¿´¿´existsºÍinµÄЧÂÊÎÊÌ⣬ÕâÀïÎÒֻ˵Ã÷Ò»ÖÖ²âÊÔÓï¾ä
set statistics io on
   sqlstatement
set statistics io off¡¢
»òÕß
set statistics time on
sqlstatement
set statistics time off
´ÓstudioÀïÃæµÄÏûÏ¢¿ÉÒÔ¿´³öÎÊÌ⣬ÎÒÒýÓÃÍøÉϵÄһЩ׼Ôòhttp://www.cnblogs.com/diction/arch ......

sqlÖ®left join¡¢right join¡¢inner joinµÄÇø±ð

left join(×óÁª½Ó) ·µ»Ø°üÀ¨×ó±íÖеÄËùÓмǼºÍÓÒ±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
right join(ÓÒÁª½Ó) ·µ»Ø°üÀ¨ÓÒ±íÖеÄËùÓмǼºÍ×ó±íÖÐÁª½á×Ö¶ÎÏàµÈµÄ¼Ç¼
inner join(µÈÖµÁ¬½Ó) Ö»·µ»ØÁ½¸ö±íÖÐÁª½á×Ö¶ÎÏàµÈµÄÐÐ
¾ÙÀýÈçÏ£º
--------------------------------------------
±íA¼Ç¼ÈçÏ£º
aID¡¡¡¡¡¡¡¡¡¡aNum
1¡¡¡¡¡¡¡¡¡¡a ......

SQL Server 2005ÖеÄT SQLÔöÇ¿C

ͨÓñí±í´ïʽ Common Table Expressions
ͨÓñí±í´ïʽ£¨CTE£©ÊÇÒ»¸ö¿ÉÒÔÓɶ¨ÒåÓï¾äÒýÓõÄÁÙʱ±íÃüÃûµÄ½á¹û¼¯¡£ÔÚËûÃǵļòµ¥ÐÎʽÖУ¬Äú¿ÉÒÔ½«CTEÊÓΪÀàËÆÓÚÊÓͼºÍÅÉÉú±í»ìºÏ¹¦ÄܵĸĽø°æ±¾¡£ÔÚ²éѯµÄfrom×Ó¾äÖÐÒýÓÃCTEµÄ·½Ê½ÀàËÆÓÚÒýÓÃÅÉÉú±íºÍÊÓͼµÄ·½Ê½¡£Ö»Ð붨ÒåCTEÒ»´Î£¬¼´¿ÉÔÚ²éѯÖжà´ÎÒýÓÃËü¡£ÔÚCTEµÄ¶¨ÒåÖУ¬¿ÉÒÔÒ ......

dz̸SQL ServerÊý¾Ý¿â²¢·¢²âÊÔ·½·¨


1. ÀûÓòâÊÔ¹¤¾ßÄ£Äâ¶à¸ö×îÖÕÓû§½øÐв¢·¢²âÊÔ; ÕâÖÖ²âÊÔ·½·¨µÄȱµã£º×îÖÕÓû§ÍùÍù²¢²»ÊÇÖ±½ÓÁ¬½Óµ½Êý¾Ý¿âÉÏ£¬¶øÊÇÒª¾­¹ýÒ»¸öºÍ¶à¸öÖмä·þÎñ³ÌÐò£¬ËùÒÔ²¢²»Äܱ£Ö¤·ÃÎÊÊý¾Ý¿âʱ»¹ÊDz¢·¢¡£Æä´Î£¬ÕâÖÖ²âÊÔ·½·¨ÐèÒªµÈµ½¿Í»§¶Ë³ÌÐò¡¢·þÎñ¶Ë³ÌÐòÈ«²¿Íê³É²ÅÄܽøÐÐ; 2. ÀûÓòâÊÔ¹¤¾ß±àд½Å±¾£¬Ö±½ÓÁ¬½ÓÊý¾Ý¿â½øÐв¢·¢²âÊÔ; ÕâÖÖ·½ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ