ÈýÖÐ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 Server 7.0 ÈëÃÅ(7)
ʱ¼ä£º2001/02/06 11:57 ×÷Õߣº¶¹¶¹ CHINAASP
¡¡ &nbs ......
¡¶SQL Sever 2005 Êý¾Ý¿â»ù´¡¼°Ó¦Óü¼Êõ½Ì³ÌÓëʵѵ ¡· ÖÜÆæ
SQL Server 2005µÄ°æ±¾£º
1. SQL Server 2005 Enterprise Edition ÆóÒµ°æ(32λºÍ64λ)
ÊÊÓÃÓÚ³¬´óÐÍÆóÒµ¡£Enterprise Edition ÊÇ×îÈ«ÃæµÄSQL Server°æ±¾£¬Êdz¬´óÐÍÆóÒµµÄÀíÏëÑ¡Ôñ£¬Äܹ»Âú×ã×ÔÓµÄÒªÇ󡣸ð汾»¹Í ......
“È«²¿µÄÎÊÌâ”ÔÚÉÏһƪÖÐÒѾд¹ýÒ»µãÁË¡£
“½ö½ö”
£ºÕâ¸öÎÊÌâ¿ÉÒÔʹÓöà±íÁ¬½ÓÀ´´¦Àí¡£ÀýÈ磺²éѯ½ö½öÑ¡ÐÞÁ˿γÌAºÍ¿Î³ÌBµÄѧÉúµÄÐÅÏ¢,sqlÈçÏ£º
ÆäÖбístudent¶¨ÒåÁËѧÉúµÄÐÅÏ¢¡£transcript¶¨ÒåÁËѧÉúµÄÑ¡¿ÎÇé¿ö¡£crscode×ֶαíʾ¿Î³Ì´úÂë¡£
select s.id
from student s,transcript t1,transc ......
1¡¢Ê¹ÓÃË÷ÒýÀ´¸ü¿ìµØ±éÀú±í¡£
ȱʡÇé¿öϽ¨Á¢µÄË÷ÒýÊÇ·ÇȺ¼¯Ë÷Òý£¬µ«ÓÐʱËü²¢²»ÊÇ×î¼ÑµÄ¡£ÔÚ·ÇȺ¼¯Ë÷ÒýÏ£¬Êý¾ÝÔÚÎïÀíÉÏËæ»ú´æ·ÅÔÚÊý¾ÝÒ³ÉÏ¡£ºÏÀíµÄË÷ÒýÉè¼ÆÒª½¨Á¢ÔÚ¶Ô¸÷ÖÖ²éѯµÄ·ÖÎöºÍÔ¤²âÉÏ¡£
Ò»°ãÀ´Ëµ£º
a.ÓдóÁ¿Öظ´Öµ¡¢ÇÒ¾³£Óз¶Î§²éѯ£¨ > ,< £¬> =,< =£©ºÍorder by¡¢group by·¢ÉúµÄÁУ¬¿É¿¼
Âǽ ......