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
Ïà¹ØÎĵµ£º
Êý¾Ý¿âʵÀý½á¹¹ ÊÂÎñÒ»ÖÂÐÔ(Transactional Consistency)ºÍ»ùÓÚʱ¼äµãµÄ»Ö¸´(Point-in-time Recovery) ϵͳԪÊý¾Ý
topÊý¾Ý¿âʵÀý½á¹¹
µ±OracleʵÀýÆô¶¯Ö®ºó£¬Ëù¿´µ½µÄ¾ÍÊÇÔÚ·þÎñÆ÷ÄÚ´æÉϵÄÒ»¸ö¸ö²»Í¬ÄÚ´æ¿é¼ÓÉϲúÉúµÄÓëÕâЩÄÚ´æ½»»¥µÄºǫ́½ø³Ì¡£OracleÎĵµ½«ÕâЩÄÚ´æ½á¹¹ºÍ½ø³ÌÊյĺÜÏêϸ¡£
ÓÉOracleʵÀýËùÕ¼ÓõÄÄÚ´æ¿é³ ......
ʹÓà ESCAPE ¹Ø¼ü×Ö¶¨ÒåתÒå·û¡£ ÔÚģʽÖУ¬µ±×ªÒå·ûÖÃÓÚͨÅä·û֮ǰʱ£¬¸ÃͨÅä·û¾Í½âÊÍΪÆÕͨ×Ö·û¡£ÀýÈ磬ҪËÑË÷ÔÚÈÎÒâλÖðüº¬×Ö·û´® 5% µÄ×Ö·û´®£¬ÇëʹÓ㺠WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
2.ESCAPE 'escape_character' ÔÊÐíÔÚ×Ö·û´®ÖÐËÑË÷ͨÅä·û¶ø²»Êǽ«Æä×÷ΪͨÅä·ûʹÓᣠescape_character ÊÇ·ÅÔÚͨÅä·ûǰ±í ......
¡¡¡¡1. SQL ServerµÄϵͳ±í
¡¡¡¡MicrosoftµÄSQL ServerÊÇÒ»¸ö¿ÉÉìËõµÄ¸ßÐÔÄÜÊý¾Ý¿â¹ÜÀíϵͳ£¬×¨Îª·Ö²¼Ê½¿Í»§»ú/·þ
ÎñÆ÷»·¾³¶øÉè¼Æ£¬SQL Server¼¸ºõ½«ËùÓеÄÅäÖÃÐÅÏ¢¡¢°²È«ÐÔÐÅÏ¢ºÍ¶ÔÏóÐÅÏ¢¶¼´æ´¢ÔÚÁËËü×Ô
ÉíµÄϵͳ±íÖУ¬¶øÏµÍ³±í´æÔÚÓÚÿ¸ö¶ÀÁ¢µÄÊý¾Ý¿âÖУ¬´æ´¢Ò»¸öÌØ¶¨Êý¾Ý¿â¶ÔÏóÐÅÏ¢µÄϵͳ±í
ͨ³£³ÆÎªÊý¾Ý¿âĿ¼£¬M ......
Microsoft SQL Server 2008 ¶Ô T-SQL ÓïÑÔ½øÐÐÁ˽øÒ»²½ÔöÇ¿¡£ÎªÁËÈÿª·¢ÈËÔ±¾¡¿ìÁ˽âÕâЩ±ä»¯£¬ÎÒÃÇÕë¶Ô 2007 Äê 6 Ô CTP °æ±¾µÄ SQL Server 2008 ÖÐµÄ T-SQL ÓïÑÔµÄÐÂÔö¹¦ÄܽøÐÐÁË·ÖÎöºÍ³¢ÊÔ¡£±¾ÎÄÃèÊö×Ô SQL Server 2008 CTP1 ÒÔÀ´ÕâЩÓïÑÔÔöÇ¿ºÍ±ä»¯¡£
¡¡¡¡±¾Îİüº¬ÈçÏÂÄÚÈÝ£º
¡¡¡¡· T-SQL Ðй¹ÔìÆ÷
¡¡¡¡&mid ......
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 ......