ÈýÖÖ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 ......
SQL³£ÓÃ×Ö·û´®º¯Êý
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼ä ......
´´½¨º¯Êý
CREATE OR REPLACE FUNCTION ntfuc(inp IN NUMBER)
RETURN NUMBER
IS
ntmp NUMBER;
BEGIN
ntmp := inp;
RETURN ntmp;
END ntfuc;
/
Ö´Ðиú¯Êýʱ
DECLARE
rcn NUMBER;
BEGIN
rcn := ntfunc(1);
END;
/ ......
ÔÚSQLÓïÑÔÖУ¬Ö»ÌṩÁËÒ»¸ö¶¯´ÊSELECTÓÃÀ´½øÐÐÊý¾Ý²éѯ²Ù×÷£¬µ«Õâ¸ö¶¯´ÊµÄ²ÎÊýÊ®·Ö¸´ÔÓ£¬ÇÒÄÜǶÌ×ʹÓã¬ÆäͨÓøñʽÈçÏ£º
SELECT [All|Distinct]<Ä¿±êÁбí´ïʽ>[£¬<Ä¿±êÁбí´ïʽ>]...
from <±íÃû»òÊÓͼÃû>[£¬<±íÃû»òÊÓͼÃû>]...
[WHERE<Ìõ¼þ±í´ïʽ>]
[GROUP BY<ÁÐÃû1>[HAVING<Ì ......