SQL¸ß¼¶Ó¦ÓÃ
Garin Zhang
×·¼Ó£ºrow_number, rank, dese_rank, ntile
1. row_number: Ϊ²éѯ³öÀ´µÄÿһÐмǼÉú³ÉÒ»¸öÐòºÅ¡£
SELECT row_number() OVER(ORDER BY field) AS row_n
from tablename;
·ÖÒ³²éѯ£º
with t_towtable
as (select row_number over(order by field1) as row_number from tb)
select * from t_rowtable where row_number > 1 and row_number > 4 order by field1;
ROW_NUMBER() OVER([<partition_by_caluse>] <order_by_clause>);
2. rank: ·µ»Ø½á¹û¼¯µÄ·ÖÇøÄÚÿÐеÄÅÅÃû£º
RANK() OVER([<partition_by_caluse_°´ÕÕij¸ö×ֶηÖÇø>]<order_by_clause_ÅÅÐò>);
Examples£º
WITH CustomerSum AS
(SELECT CustomerID, SUM(totalDue) AS total
from sales GROUP BY CustomerID)
SELECT * RANK() OVER (ORDER BY total DESC) AS RANK
from CustomerSum
3. dense_rank: ˳ÐòÅÅÐò£¨Éú³ÉµÄÐòºÅÊÇÁ¬ÐøµÄ£©¡£
4. ntile£ºÈ¡Ç°¶àÉÙ¸öÃû´ÎµÄÅÅÃûµÄÐС£
SELECT SalesID, NTILE(1000) OVER(ORDER BY CustomerID) AS NTile
from Sales
ͰÊýµÄ¼ÆË㣺
if(¼Ç¼Êý%ͰÊý == 0)
ÿͰ¼Ç¼Êý¶¼Îª¼ÇÊý×ÜÊý/ͰÊý
else
recordCount1 = total/tongNum + 1;
int n = 1;
m = recordCont1*n;
while((total - m) % (tongNum - n)) != 0)
{
n++;
m = recordCount1 * n;
}
recordCount2 = (total - m) / (tongNum - n);
½«Ç°n¸öͰ¼Ç¼ÉèÖÃΪrecordCount1
½«n+1¸öµ½ºóÃæËùÓÐͰ¼ÇΪrecordCount2
Ïà¹ØÎĵµ£º
xtype ´ú±íÀàÐÍ
C = CHECK Ô¼Êø
D = ĬÈÏÖµ»ò DEFAULT Ô¼Êø
F = FOREIGN KEY Ô¼Êø
L = ÈÕÖ¾
FN = ±êÁ¿º¯Êý
IF = ÄÚǶ±íº¯Êý
P = ´æ´¢¹ý³Ì
PK = PRIMARY KEY Ô¼Êø£¨ÀàÐÍÊÇ K£©
RF = ¸´ÖÆÉ¸Ñ¡´æ´¢¹ý³Ì
S = ϵͳ±í
TF = ±íº¯Êý
TR = ´¥·¢Æ÷
U = Óû§±í
UQ = UNIQUE Ô¼Êø£¨ÀàÐÍÊÇ K£©
V = ÊÓͼ ......
¡¡¡¡1. SQL ServerµÄϵͳ±í
¡¡¡¡MicrosoftµÄSQL ServerÊÇÒ»¸ö¿ÉÉìËõµÄ¸ßÐÔÄÜÊý¾Ý¿â¹ÜÀíϵͳ£¬×¨Îª·Ö²¼Ê½¿Í»§»ú/·þ
ÎñÆ÷»·¾³¶øÉè¼Æ£¬SQL Server¼¸ºõ½«ËùÓеÄÅäÖÃÐÅÏ¢¡¢°²È«ÐÔÐÅÏ¢ºÍ¶ÔÏóÐÅÏ¢¶¼´æ´¢ÔÚÁËËü×Ô
ÉíµÄϵͳ±íÖУ¬¶øÏµÍ³±í´æÔÚÓÚÿ¸ö¶ÀÁ¢µÄÊý¾Ý¿âÖУ¬´æ´¢Ò»¸öÌØ¶¨Êý¾Ý¿â¶ÔÏóÐÅÏ¢µÄϵͳ±í
ͨ³£³ÆÎªÊý¾Ý¿âĿ¼£¬M ......
Ò» ϵͳԤ¶¨Òå´íÎó´úÂë
SQL Server ÓÐ3831¸öÔ¤¶¨Òå´íÎó´úÂ룬ÓÉmaster.dbo.sysmessages±íά»¤¡£Ã¿Ò»¸ö´íÎó´úÂë¶¼ÓÐÏàÓ¦µÄ¼¶±ðºÍÃèÊö¡£
´íÎó¶¨ÒåµÄ¼¶±ð´Ó0µ½25¡£20ÒÔÉϵĴíÎó´ú±íÖØ´ó´íÎó£¬Í¨³£ÒâζןôíÎó»áµ¼Ö´洢½ø³ÌÁ¢¿ÌÖÕÖ¹£¬²¢ÇÒËùÓеĿͻ§Á¬½Ó¶¼ÒªÖØÐ³õʼ»¯¡£
·Ç¹Ø¼üÐÔ´íÎóÖ»ÊǽûÖ¹µôµ±Ç°ÔËÐеijÌÐòÐУ¬²¢¼Ì ......
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
fr ......