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  
Ïà¹ØÎĵµ£º
ÔÎÄ:http://www.blogjava.net/Unmi/archive/2009/01/05/249956.html
ÔÚ Oracle Êý¾Ý¿âÖУ¬ÎÒÃÇͨ³£ÔÚ²»Í¬Êý¾Ý¿âµÄ±í¼ä¼Ç¼½øÐи´ÖÆ»òÇ¨ÒÆÊ±»áÓÃÒÔϼ¸ÖÖ·½·¨£º
1. A ±íµÄ¼Ç¼µ¼³öΪһÌõÌõ·ÖºÅ¸ô¿ªµÄ insert Óï¾ä£¬È»ºóÖ´ÐвåÈëµ½ B ±íÖÐ
2. ½¨Á¢Êý¾Ý¿â¼äµÄ dblink£¬È»ºóÓà create table B as select * from A ......
TOM´óÊåµÄµ÷ÓÅÊéÖÐ˵¹ýÒ»¾ä»°£¬¾ßÌåÓ¢ÎľÍÍüÁË£¬´ó¸ÅÒâ˼¾ÍÊÇ£ºÈç¹ûÓÐÈËÈÃÎÒд±¾ÔõÑùÈÃORACLEÐÔÄÜ×îÂýµÄÊéµÄ»°ÎһὫȡÏû°ó¶¨±äÁ¿(bind variable)×öΪÊéµÄµÚÒ»ÕºÍ×îºóÒ»ÕÂ(ËûµÄÒâ˼ÊÇËûºÜÓÐÓÄĬ~~!)£¬¿É¼û°ó¶¨±äÁ¿µÄÖØÒªÐÔ¡£
°ó¶¨±äÁ¿´ó¶àÓÃÔÚOLTP(online transaction process)ÖУ¬ÔÚOLAP(online analizy process)ÖоÍû± ......
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓбí
SELECT table_name from user_tables;
-- ²é¿´ORACLE Êý¾Ý¿âÖÐËùÓÐÓû§ÏµÄËùÓбí
select user,table_name from all_tables;
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓÐÁÐ
select table_name,column_name from user_tab_columns;
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓÐÁÐ
se ......
TOP ÔöÇ¿¡£¿ÉÒÔÖ¸¶¨Ò»¸öÊý×Ö±í´ïʽ£¬ÒÔ·µ»ØÒªÍ¨¹ý²éѯӰÏìµÄÐÐÊý»ò°Ù·Ö±È£¬»¹¿ÉÒÔ¸ù¾ÝÇé¿öʹÓñäÁ¿»ò×Ó²éѯ¡£
¿ÉÒÔÔÚDELETE¡¢UPDATEºÍINSERT²éѯÖÐʹÓÃTOPÑ¡Ïî¡£
2¡¢¸üºÃµØÌæ»»SET ROWCOUNTÑ¡Ïʹ֮¸üΪÓÐЧ¡£
OUTPUT
1¡¢SQL Server 2005ÒýÈëÒ»¸öеÄOUTPUT×Ӿ䣬ÒÔʹÄú¿ÉÒÔ³åÐÞ¸ÄÓï¾ä(INSERT¡¢UPDATE¡¢DELETE)ÖÐ ......