ÈýÖÖSQL·ÖÒ³Óï¾ä
À´×Ô£ºhttp://www.cnblogs.com/morningwang/archive/2009/01/02/1367277.html
ÓйطÖÒ³ SQL µÄ×ÊÁϺܶ࣬ÓеÄʹÓô洢¹ý³Ì£¬ÓеÄʹÓÃÓαꡣ±¾È˲»Ï²»¶Ê¹ÓÃÓα꣬ÎÒ¾õµÃËüºÄ×Ê¡¢Ð§Âʵͣ»Ê¹Óô洢¹ý³ÌÊǸö²»´íµÄÑ¡Ôñ£¬ÒòΪ´æ´¢¹ý³ÌÊǾ¹ýÔ¤±àÒëµÄ£¬Ö´ÐÐЧÂʸߣ¬Ò²¸üÁé»î¡£ÏÈ¿´¿´µ¥Ìõ SQL Óï¾äµÄ·ÖÒ³ SQL °É¡£
·½·¨1£º
ÊÊÓÃÓÚ SQL Server 2000/2005
SELECT TOP Ò³´óС *
from table1
WHERE id NOT IN
(
SELECT TOP Ò³´óС*(Ò³Êý-1) id from table1 ORDER BY id
)
ORDER BY id
·½·¨2£º
ÊÊÓÃÓÚ SQL Server 2000/2005
SELECT TOP Ò³´óС *
from table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
from
(
SELECT TOP Ò³´óС*(Ò³Êý-1) id from table1 ORDER BY id
) A
)
ORDER BY id
·½·¨3£º
ÊÊÓÃÓÚ SQL Server 2005
SELECT TOP Ò³´óС *
from
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* from table1
) A
WHERE RowNumber > Ò³´óС*(Ò³Êý-1)
˵Ã÷£¬Ò³´óС£ºÃ¿Ò³µÄÐÐÊý£»Ò³Êý£ºµÚ¼¸Ò³¡£Ê¹ÓÃʱ£¬Çë°Ñ“Ò³´óС”ºÍ“Ò³´óС*(Ò³Êý-1)”Ìæ»»³ÉÊý×Ö¡£
ÆäËüµÄ·½°¸£ºÈç¹ûûÓÐÖ÷¼ü£¬¿ÉÒÔÓÃÁÙʱ±í£¬Ò²¿ÉÒÔÓ÷½°¸Èý×ö£¬µ«ÊÇЧÂÊ»áµÍ¡£
½¨ÒéÓÅ»¯µÄʱºò£¬¼ÓÉÏÖ÷¼üºÍË÷Òý£¬²éѯЧÂÊ»áÌá¸ß¡£
ͨ¹ýSQL ²éѯ·ÖÎöÆ÷£¬ÏÔʾ±È½Ï£ºÎҵĽáÂÛÊÇ:
·ÖÒ³·½°¸¶þ£º(ÀûÓÃID´óÓÚ¶àÉÙºÍSELECT TOP·ÖÒ³£©Ð§ÂÊ×î¸ß£¬ÐèҪƴ½ÓSQLÓï¾ä
·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³) ЧÂÊ´ÎÖ®£¬ÐèҪƴ½ÓSQLÓï¾ä
·ÖÒ³·½°¸Èý£º(ÀûÓÃSQLµÄÓαê´æ´¢¹ý³Ì·ÖÒ³) ЧÂÊ×î²î£¬µ«ÊÇ×îΪͨÓÃ
Ïà¹ØÎĵµ£º
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,(sele ......
SQL UNION ²Ù×÷·û
UNION ²Ù×÷·ûÓÃÓںϲ¢Á½¸ö»ò¶à¸ö SELECT Óï¾äµÄ½á¹û¼¯¡£
Çë×¢Ò⣬UNION ÄÚ²¿µÄ SELECT Óï¾ä±ØÐëÓµÓÐÏàͬÊýÁ¿µÄÁС£ÁÐÒ²±ØÐëÓµÓÐÏàËƵÄÊý¾ÝÀàÐÍ¡£Í¬Ê±£¬Ã¿Ìõ SELECT Óï¾äÖеÄÁеÄ˳Ðò±ØÐëÏàͬ¡£
SQL UNION Óï·¨
SELECT column_name(s) from table_name1
UNION
SELECT column_name(s) from table_na ......
1¡¢¶¨Òå»ù±¾±í
SQLÓïÑÔʹÓö¯´ÊCREATE¶¨Òå»ù±¾±í£¬Æä¾ßÌåÓï·¨¸ñʽÈçÏ£º
CREATE TABLE <±íÃû>
(<ÁÐÃû><Êý¾ÝÀàÐÍ>[Áм¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ]...[£¬<ÁÐÃû><Êý¾ÝÀàÐÍ>[Áм¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ]][,<±í¼¶ÍêÕûÐÔÔ¼ÊøÌõ¼þ>])£»
ÀýÈ磺½¨Á¢Ò»¸öѧÉú±íStudent£¬ËüÓÉѧºÅSno£¬ÐÕÃûSname£¬ÐÔ±ðSsex£¬Äê ......
SQL Server ¾Ñé £¨×ªÔØ£©
http://www.cnblogs.com/treeyh/archive/2007/08/06/844763.html
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉ ......
1£ºSQL ServerÊý¾Ý¿âÅäÖÃ
¿ªÆô·þÎñÆ÷
ÍÐÅÌÏÔʾ·þÎñÆ÷Æô¶¯
2:ÔÚStaAfx.h ÖÐÌí¼ÓÈçÏ´úÂë
#import "C:\\Program Files\\Common Files\\System\\ado\\msado15.dll" no_namespace rename("EOF","adoEOF")rena ......