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  
Ïà¹ØÎĵµ£º
²½ÖèÊÇ£º
1£©Ìí¼Ó/ɾ³ý³ÌÐòÖг¹µ×ɾ³ýsql server¡£
2£©½«Ã»ÓÐɾ³ýµÄsql serverĿ¼Ҳɾ³ýµô¡£
3£©´ò¿ª×¢²á±í±à¼Æ÷£¬ÔÚHKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session ManagerÖÐÕÒµ½PendingFileRenameOperationsÏîÄ¿£¬²¢É¾³ýËü¡£ÕâÑù¾Í¿ÉÒÔÇå³ý°²×°ÔݹÒÏîÄ¿¡£
4£©É¾³ý×¢²á±íÖиúsql serverÏà¹ØµÄ¼ü¡ ......
Ò»¡¢ÒòÇéÖÆÒË£¬½¨Á¢“Êʵ±”µÄË÷Òý
½¨Á¢“Êʵ±”µÄË÷ÒýÊÇʵÏÖ²éѯÓÅ»¯µÄÊ×ҪǰÌá¡£
Ë÷Òý£¨index£©Êdzý±íÖ®ÍâÁíÒ»ÖØÒªµÄ¡¢Óû§¶¨ÒåµÄ´æ´¢ÔÚÎïÀí½éÖÊÉϵÄÊý¾Ý½á¹¹¡£µ±¸ù¾ÝË÷ÒýÂëµÄÖµËÑË÷Êý¾Ýʱ£¬Ë÷ÒýÌṩÁ˶ÔÊý¾ÝµÄ¿ìËÙ·ÃÎÊ¡£ÊÂʵÉÏ£¬Ã»ÓÐË÷Òý,Êý¾Ý¿âÒ²Äܸù¾ÝSELECTÓï¾ä³É¹¦µØ¼ìË÷µ½½á¹û£¬µ«Ëæ×űí±ä ......
1¡¢²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
¡¡¡¡select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
¡¡¡¡from dba_tablespaces t, dba_data_files d
¡¡¡¡where t.tablespace_name = d.tablespace_name
¡¡¡¡group by t.tablespace_name;
¡¡¡¡
¡¡¡¡2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ¼°´óС
¡¡¡¡select tablespace_ ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CR ......
¿âλ »õÎï±àºÅ ¿â´æÊý
1 0101 50
1 0102 60
1 0103 50
2 0101 90
2 0103 100
2 0111 30
3 0101 12 ......