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
Ïà¹ØÎĵµ£º
²Ù×÷ϵͳµÄÖ§³Ö °æ±¾ºÍ·¢Ðаæ ʵÀý¡¢Êý¾Ý¿âºÍ±í¿Õ¼ä ʵÀýÃûºÍSID ϵͳÊý¾Ý¿âºÍϵͳ±í¿Õ¼ä
Ò»°ãµÄ¹«Ë¾Í¨³£»áÔÚËûÃǵÄÐÅϢϵͳ¼Ü¹¹ÖÐÒýÈë¶àÖÖÊý¾Ý¿âƽ̨£¬Í¬Ê±ÒýÈëÈýµ½ËÄÖÖ²»Í¬µÄRDBMS½â¾ö·½°¸µÄÖдóÐ͹«Ë¾Ò²²¢²»ÉÙ¼û£¬µ±È»ÕâЩ¹«Ë¾ÀïÃæµÄDBAÃÇͨ³£Ò²ÐèҪͬʱӵÓйÜÀí¶àÖÖ²»Í¬Æ½Ì¨µÄ¼¼ÄÜÁË¡£
Ö»ÔÚÒ»ÖÖÆ½Ì¨ÉÏÕ¹¿ª¹¤×÷µÄÊý¾Ý ......
ʹÓà ESCAPE ¹Ø¼ü×Ö¶¨ÒåתÒå·û¡£ ÔÚģʽÖУ¬µ±×ªÒå·ûÖÃÓÚͨÅä·û֮ǰʱ£¬¸ÃͨÅä·û¾Í½âÊÍΪÆÕͨ×Ö·û¡£ÀýÈ磬ҪËÑË÷ÔÚÈÎÒâλÖðüº¬×Ö·û´® 5% µÄ×Ö·û´®£¬ÇëʹÓ㺠WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
2.ESCAPE 'escape_character' ÔÊÐíÔÚ×Ö·û´®ÖÐËÑË÷ͨÅä·û¶ø²»Êǽ«Æä×÷ΪͨÅä·ûʹÓᣠescape_character ÊÇ·ÅÔÚͨÅä·ûǰ±í ......
sql Ìí¼ÓзÖÇø
ALTER PARTITION SCHEME SCH_Source_ID_DT_ID
NEXT USED sn3
go
ALTER PARTITION FUNCTION [PF_Source_ID_DT_ID]()
SPLIT RANGE ('350000')
ɾ³ý·ÖÇø
±à¼
ALTER PARTITION FUNCTION [PF_Source_ID_DT_ID]( ......
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......
´ÓTable ±íÖÐÈ¡³öµÚ m Ìõµ½µÚ n ÌõµÄ¼Ç¼£º(Not In °æ±¾)
SELECT TOP n-m+1 *
from Table
WHERE (id NOT IN (SELECT TOP m-1 id from Table ))
--´ÓTABLE±íÖÐÈ¡³öµÚmµ½nÌõ¼Ç¼ (Exists°æ±¾)
SELECT TOP n-m+1 * from TABLE AS a WHERE Not Exists
(Select * from (Select Top m-1 * from TABLE orde ......