Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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                                    


Ïà¹ØÎĵµ£º

SQLµ¼³öµ½ExcelµÄÓï¾ä

sqlµ¼³öµ½Excel
´ÓExcelÎļþÖÐ,µ¼ÈëÊý¾Ýµ½SQLÊý¾Ý¿âÖÐ,ºÜ¼òµ¥,Ö±½ÓÓÃÏÂÃæµÄÓï¾ä:
/*===================================================================*/
--Èç¹û½ÓÊÜÊý¾Ýµ¼ÈëµÄ±íÒѾ­´æÔÚ
insert into ±í select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1 ......

SQLÈý´ó·¶Ê¾

µÚÒ»·¶Ê½:È·±£Ã¿ÁеÄÔ­×ÓÐÔ.
Èç¹ûÿÁÐ(»òÕßÿ¸öÊôÐÔ)¶¼ÊDz»¿ÉÔÙ·ÖµÄ×îСÊý¾Ýµ¥Ôª(Ò²³ÆÎª×îСµÄÔ­×Óµ¥Ôª),ÔòÂú×ãµÚÒ»·¶Ê½.
ÀýÈç:¹Ë¿Í±í(ÐÕÃû¡¢±àºÅ¡¢µØÖ·¡¢……)ÆäÖÐ"µØÖ·"Áл¹¿ÉÒÔϸ·ÖΪ¹ú¼Ò¡¢Ê¡¡¢ÊС¢ÇøµÈ¡£
µÚ¶þ·¶Ê½:ÔÚµÚÒ»·¶Ê½µÄ»ù´¡Éϸü½øÒ»²ã,Ä¿±êÊÇÈ·±£±íÖеÄÿÁж¼ºÍÖ÷¼üÏà¹Ø.
Èç¹ûÒ»¸ö¹ØÏµÂú×ã ......

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

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

SQLÓï¾ä¼áÐÐתºáÁеÄÒ»ÖÖ·½·¨

¿âλ  »õÎï±àºÅ ¿â´æÊý
1     0101     50
1     0102     60
1     0103     50
2     0101     90
2     0103     100
2     0111     30
3     0101     12 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ