Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ÈýÖÐ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 ... sqlÓï¾äÖеÄ1´ú±íʲôÒâ˼£¿

select 1 from table;Óëselect anycol(Ä¿µÄ±í¼¯ºÏÖеÄÈÎÒâÒ»ÐУ© from table;Óëselect * from table ´Ó×÷ÓÃÉÏÀ´ËµÊÇûÓвî±ðµÄ£¬¶¼ÊDz鿴ÊÇ·ñÓмǼ£¬Ò»°ãÊÇ×÷Ìõ¼þ²éѯÓõġ£select 1 from ÖеÄ1ÊÇÒ»³£Á¿£¨¿ÉÒÔΪÈÎÒâÊýÖµ£©£¬²éµ½µÄËùÓÐÐеÄÖµ¶¼ÊÇËü£¬µ«´ÓЧÂÊÉÏÀ´Ëµ£¬1>anycol>*£¬ÒòΪ²»Óòé×Öµä±í¡£ ......

SQLǶÌ××Ó²éѯ

×Ô£ºhttp://blog.csdn.net/AJAXBloger/archive/2007/08/29/1764506.aspx
×Ó²éѯ£º
ʹÓÃ×Ó²éѯµÄÔ­Ôò
1.Ò»¸ö×Ó²éѯ±ØÐë·ÅÔÚÔ²À¨ºÅÖС£
2.½«×Ó²éѯ·ÅÔڱȽÏÌõ¼þµÄÓÒ±ßÒÔÔö¼Ó¿É¶ÁÐÔ¡£
×Ó²é
ѯ²»°üº¬ ORDER BY ×Ӿ䡣¶ÔÒ»¸ö SELECT Óï¾äÖ»ÄÜÓÃÒ»¸ö ORDER BY ×Ӿ䣬
²¢ÇÒÈç¹ûÖ¸¶¨ÁËËü¾Í±ØÐë·ÅÔÚÖ÷
SELECT Óï¾äµÄ×î ......

SQL With cube

ÏÐÀ´Ð´ÏÂwith cubeµÄÓ÷¨
cubeÔËËã·ûÔÚ SELECT Óï¾äµÄ GROUP BY ×Ó¾äÖÐÖ¸¶¨¡£¸ÃÓï¾äµÄÑ¡ÔñÁбíÓ¦°üº¬Î¬¶ÈÁк;ۺϺ¯Êý±í´ïʽ¡£GROUP BY Ó¦Ö¸¶¨Î¬¶ÈÁк͹ؼü×Ö WITH CUBE¡£½á¹û¼¯½«°üº¬Î¬¶ÈÁÐÖи÷ÖµµÄËùÓпÉÄÜ×éºÏ£¬ÒÔ¼°ÓëÕâЩά¶ÈÖµ×éºÏÏàÆ¥ÅäµÄ»ù´¡ÐÐÖеľۺÏÖµ¡£
ÏÈ¿´ÏÂ±í£º
ÎÒÃÇÒÔid¾ÛºÏ²éѯ³öƽ¾ù·Ö
ÕâÒ»ÌõSQLÓï¾äÓ ......

SqlÊý¾ÝÓÅ»¯·½°¸

1¡¢Ê¹ÓÃË÷ÒýÀ´¸ü¿ìµØ±éÀú±í¡£
ȱʡÇé¿öϽ¨Á¢µÄË÷ÒýÊÇ·ÇȺ¼¯Ë÷Òý£¬µ«ÓÐʱËü²¢²»ÊÇ×î¼ÑµÄ¡£ÔÚ·ÇȺ¼¯Ë÷ÒýÏ£¬Êý¾ÝÔÚÎïÀíÉÏËæ»ú´æ·ÅÔÚÊý¾ÝÒ³ÉÏ¡£ºÏÀíµÄË÷ÒýÉè¼ÆÒª½¨Á¢ÔÚ¶Ô¸÷ÖÖ²éѯµÄ·ÖÎöºÍÔ¤²âÉÏ¡£
Ò»°ãÀ´Ëµ£º
a.ÓдóÁ¿Öظ´Öµ¡¢ÇÒ¾­³£Óз¶Î§²éѯ£¨ > ,< £¬> =,< =£©ºÍorder by¡¢group by·¢ÉúµÄÁУ¬¿É¿¼
Âǽ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ