ÈýÖÐSQL ·ÖÒ³·½·¨Ð§ÂÊ·ÖÎö
ÈýÖÖSQL·ÖÒ³·¨Ð§ÂÊ·ÖÎö
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
¡¡Óï¾äÐÎʽ£ºÀûÓÃNot InºÍSELECT TOP·ÖÒ³) ЧÂÊÖУ¬ÐèҪƴ½ÓSQLÓï¾ä
SELECT TOP 10 * from TestTable WHERE (Id NOT IN (SELECT TOP 20 id from TestTable ORDER BY id )) ORDER BY ID
2.·ÖÒ³·½°¸¶þ£º(ÀûÓÃID´óÓÚ¶àÉÙºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£ºÀûÓÃID´óÓÚ¶àÉÙºÍSELECT TOP·ÖÒ³)ЧÂÊ×î¸ß£¬ÐèҪƴ½ÓSQLÓï¾ä
SELECT TOP 10 * from TestTable WHERE (ID > (SELECT MAX(id) from (SELECT TOP 20 id from TestTable ORDER BY id) AST))
3.·ÖÒ³·½°¸Èý£º(ÀûÓÃSQLµÄÓÎ±ê´æ´¢¹ý³Ì·ÖÒ³)
Óï¾äÐÎʽ£ºÀûÓÃSQLµÄÓÎ±ê´æ´¢¹ý³Ì·ÖÒ³) ЧÂÊ×î²î£¬µ«ÊÇ×îΪͨÓÃ
create procedure SqlPager
@sqlstr nvarchar(4000), --²éѯ×Ö·û´®
@currentpage int, --µÚNÒ³
@pagesize int --ÿҳÐÐÊý
as
set nocount on
declare @P1 int, --P1ÊÇÓαêµÄid
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as ×ÜÒ³Êý--,@rowcount as ×ÜÐÐÊý,@currentpage as µ±Ç°Ò³
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
Ïà¹ØÎĵµ£º
select 1 from table;Óëselect anycol(Ä¿µÄ±í¼¯ºÏÖеÄÈÎÒâÒ»ÐУ© from table;Óëselect * from table ´Ó×÷ÓÃÉÏÀ´ËµÊÇûÓвî±ðµÄ£¬¶¼ÊDz鿴ÊÇ·ñÓмǼ£¬Ò»°ãÊÇ×÷Ìõ¼þ²éѯÓõġ£select 1 from ÖеÄ1ÊÇÒ»³£Á¿£¨¿ÉÒÔΪÈÎÒâÊýÖµ£©£¬²éµ½µÄËùÓÐÐеÄÖµ¶¼ÊÇËü£¬µ«´ÓЧÂÊÉÏÀ´Ëµ£¬1>anycol>*£¬ÒòΪ²»Óòé×Öµä±í¡£ ......
¡¶SQL Sever 2005 Êý¾Ý¿â»ù´¡¼°Ó¦Óü¼Êõ½Ì³ÌÓëʵѵ ¡· ÖÜÆæ
SQL Server 2005µÄ°æ±¾£º
1. SQL Server 2005 Enterprise Edition ÆóÒµ°æ(32λºÍ64λ)
ÊÊÓÃÓÚ³¬´óÐÍÆóÒµ¡£Enterprise Edition ÊÇ×îÈ«ÃæµÄSQL Server°æ±¾£¬Êdz¬´óÐÍÆóÒµµÄÀíÏëÑ¡Ôñ£¬Äܹ»Âú×ã×ÔÓµÄÒªÇ󡣸ð汾»¹Í ......
ѧϰsqlµÄ±Ø¾ÎÊÌâ¡£
ѧÉú±ístudent (idѧºÅ SnameÐÕÃû SdeptËùÔÚϵ)
¿Î³Ì±íCourse (crscode¿Î³ÌºÅ name¿Î³ÌÃû)
ѧÉúÑ¡¿Î±ítranscript (studidѧºÅ &nbs ......
“È«²¿µÄÎÊÌâ”ÔÚÉÏһƪÖÐÒѾд¹ýÒ»µãÁË¡£
“½ö½ö”
£ºÕâ¸öÎÊÌâ¿ÉÒÔʹÓöà±íÁ¬½ÓÀ´´¦Àí¡£ÀýÈ磺²éѯ½ö½öÑ¡ÐÞÁ˿γÌAºÍ¿Î³ÌBµÄѧÉúµÄÐÅÏ¢,sqlÈçÏ£º
ÆäÖбístudent¶¨ÒåÁËѧÉúµÄÐÅÏ¢¡£transcript¶¨ÒåÁËѧÉúµÄÑ¡¿ÎÇé¿ö¡£crscode×ֶαíʾ¿Î³Ì´úÂë¡£
select s.id
from student s,transcript t1,transc ......
ÏÐÀ´Ð´ÏÂwith cubeµÄÓ÷¨
cubeÔËËã·ûÔÚ SELECT Óï¾äµÄ GROUP BY ×Ó¾äÖÐÖ¸¶¨¡£¸ÃÓï¾äµÄÑ¡ÔñÁбíÓ¦°üº¬Î¬¶ÈÁк;ۺϺ¯Êý±í´ïʽ¡£GROUP BY Ó¦Ö¸¶¨Î¬¶ÈÁк͹ؼü×Ö WITH CUBE¡£½á¹û¼¯½«°üº¬Î¬¶ÈÁÐÖи÷ÖµµÄËùÓпÉÄÜ×éºÏ£¬ÒÔ¼°ÓëÕâЩά¶ÈÖµ×éºÏÏàÆ¥ÅäµÄ»ù´¡ÐÐÖеľۺÏÖµ¡£
ÏÈ¿´ÏÂ±í£º
ÎÒÃÇÒÔid¾ÛºÏ²éѯ³öƽ¾ù·Ö
ÕâÒ»ÌõSQLÓï¾äÓ ......