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
Ïà¹ØÎĵµ£º
SQLµÄ²Ù×÷£º
E.g.
judging-±íÃû
userid, name, password-±íÖеĹؼü×Ö
Ìí¼Ó£º
string sql = "insert into judging (userid,name,password) values('" + userid.Text.Trim() + "','" + name.Text.Trim() + "','" + password.Text.Trim() + "')";
ɾ³ý£º
string sql = "delete from judging where userid=" + ......
Æäʵɾ³ýÊý¾Ý¿âÖÐÊý¾ÝµÄ·½·¨²¢²»¸´ÔÓ£¬ÎªÊ²Ã´ÎÒ»¹Òª¶à´ËÒ»¾ÙÄØ£¬Ò»ÊÇÎÒÕâÀï½éÉܵÄÊÇɾ³ýÊý¾Ý¿âµÄËùÓÐÊý¾Ý£¬ÒòΪÊý¾ÝÖ®¼ä¿ÉÄÜÐγÉÏà»¥Ô¼Êø¹ØÏµ£¬É¾³ý²Ù×÷¿ÉÄÜÏÝÈëËÀÑ»·£¬¶þÊÇÕâÀïʹÓÃÁË΢ÈíδÕýʽ¹«¿ªµÄsp_MSForEachTable´æ´¢¹ý³Ì¡£
Ò²ÐíºÜ¶à¶ÁÕßÅóÓѶ¼¾Àú¹ýÕâÑùµÄÊÂÇ飺ҪÔÚ¿ª·¢Êý¾Ý¿â»ù´¡ÉÏÇåÀíÒ»¸ö¿Õ¿â£¬µ«ÓÉÓÚ¶ÔÊý¾Ý¿ ......
Microsoft SQL Server 2008 ¶Ô T-SQL ÓïÑÔ½øÐÐÁ˽øÒ»²½ÔöÇ¿¡£ÎªÁËÈÿª·¢ÈËÔ±¾¡¿ìÁ˽âÕâЩ±ä»¯£¬ÎÒÃÇÕë¶Ô 2007 Äê 6 Ô CTP °æ±¾µÄ SQL Server 2008 ÖÐµÄ T-SQL ÓïÑÔµÄÐÂÔö¹¦ÄܽøÐÐÁË·ÖÎöºÍ³¢ÊÔ¡£±¾ÎÄÃèÊö×Ô SQL Server 2008 CTP1 ÒÔÀ´ÕâЩÓïÑÔÔöÇ¿ºÍ±ä»¯¡£
¡¡¡¡±¾Îİüº¬ÈçÏÂÄÚÈÝ£º
¡¡¡¡· T-SQL Ðй¹ÔìÆ÷
¡¡¡¡&mid ......
Ó¦ÓÃÖз¢ÏÖsqlÖеÄand¼°orµÄÖ´ÐÐЧÂÊÎÊÌâ
£ó£ñ£ìÓï¾ä£¬ÎªÊ²Ã´°Ñ×îºóµÄ£ï£ò»»³É£á£î£ä£¬²éѯµÄ¾ÍºÜ¿ì£¬Ê¹ÓõÄÊÇmssql·¶ÀýÖÐnorthwindÊý¾Ý¿âΪÀý£¬
select * from Orders a left join [Order Details] b on a.orderid = b.orderid
where a.customerid like '%ics%' or b.productid in (42,72)
×·×ÙÁËÓï¾äµÄÖ´Ðз½°¸,·¢ÏÖ ......
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 ......