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  
Ïà¹ØÎĵµ£º
Microsoft SQL server2000ÓÉһϵÁÐÏ໥Ð×÷µÄ×é¼þ¹¹³É¡£ÄÜÂú×ã×î´óµÄWEBÕ¾µãºÍÆóÒµÊý¾Ý´¦Àíϵͳ´æ´¢ºÍ·ÖÎöÊý¾ÝµÄÐèÒª¡£±¾ÎÄ´øÀ´µÄ¾ÍÊÇÔÚwindows»·¾³ÏÂSQL2000µÄ°²×°¹ý³Ì¡£
ϵͳÐèÇó
ÕâÀïÒÔÆäËĺÏÒ»°æ±¾ÎªÀý£¬ÒÔϰ²×°¹ý³ÌͬÀí¡£Microsoft SQL Server 2000 ¿ÉÔÚÔËÐÐ Intel»ò¼æÈÝµÄ Pentium¡¢Pentium Pr ......
sqlµ¼³öµ½Excel
´ÓExcelÎļþÖÐ,µ¼ÈëÊý¾Ýµ½SQLÊý¾Ý¿âÖÐ,ºÜ¼òµ¥,Ö±½ÓÓÃÏÂÃæµÄÓï¾ä:
/*===================================================================*/
--Èç¹û½ÓÊÜÊý¾Ýµ¼ÈëµÄ±íÒѾ´æÔÚ
insert into ±í select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1 ......
Ê×ÏÈ£¬ÎÒÃÇ¿´¿´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 ......
ͨÓñí±í´ïʽ Common Table Expressions
ͨÓñí±í´ïʽ£¨CTE£©ÊÇÒ»¸ö¿ÉÒÔÓɶ¨ÒåÓï¾äÒýÓõÄÁÙʱ±íÃüÃûµÄ½á¹û¼¯¡£ÔÚËûÃǵļòµ¥ÐÎʽÖУ¬Äú¿ÉÒÔ½«CTEÊÓΪÀàËÆÓÚÊÓͼºÍÅÉÉú±í»ìºÏ¹¦ÄܵĸĽø°æ±¾¡£ÔÚ²éѯµÄfrom×Ó¾äÖÐÒýÓÃCTEµÄ·½Ê½ÀàËÆÓÚÒýÓÃÅÉÉú±íºÍÊÓͼµÄ·½Ê½¡£Ö»Ð붨ÒåCTEÒ»´Î£¬¼´¿ÉÔÚ²éѯÖжà´ÎÒýÓÃËü¡£ÔÚCTEµÄ¶¨ÒåÖУ¬¿ÉÒÔÒ ......