Sql Server2005ÓÃRow_Number·ÖÒ³´æ´¢¹ý³ÌµÄÐÔÄÜʵ²â
Sql Server2005µÄÒ»¸öÐÂÌØÐÔ±ãÊÇÎÒµÈÁ˺ܾõÄRow_Number(),ÒÔǰÓÃOracleʱÓÃrownumberд·ÖÒ³´æ´¢¹ý³ÌºÜ·½±ã£º£©
ÏÂÃæÊÇÎÒ×öµÄÒ»¸öССµÄ²âÊÔ£¬²âÊÔÎÒÔÀ´ÔÚsql server2000ÏÂËùÓõķÖÒ³´æ´¢¹ý³ÌÓëʹÓÃRow_Number()±àдµÄ´æ´¢¹ý³ÌÔÚSql Server2005ÉϵÄÖ´ÐÐЧÂÊ...
Êý¾Ý±í£º
REATE TABLE [dbo].[test](
[UserId] [int] Primary Key IDENTITY(1,1) ,
[UserName] [nvarchar](256) ,
[Sex] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Address] [varchar](100) ,
[status] [bit] NULL,
[Email] [varchar](100) ,
[InsertDate] [datetime] NOT NULL
)
²åÈë1000k¼Ç¼
use temp
Go
declare @n int
set @n = 0
while @n<1000000
BEGIN
Insert Into test(UserName,Sex,Age,Address,status,Email,InsertDate)
Values('bbisky','ÄÐ','25','Öйú´«Ã½´óѧÏÖ´úÔ¶³Ì½ÌÓýÖÐÐÄ',1,'denghaibo@live.com',getdate())
Select @n = @n+1
END
Á½¸ö´æ´¢¹ý³Ì
ÔÀ´Ê¹ÓÃTopµÄ·ÖÒ³´æ´¢¹ý³Ì
Create proc [dbo].[test_PageById]
(
@pageIndex int,
@pageSize int
)
AS
SELECT TOP(@pageSize) *
from test
WHERE UserId <
(SELECT MIN(UserId) from (
SELECT TOP ((@pageIndex-1) * @pageSize) UserId
from test
ORDER BY UserId DESC)B )
ORDER BY UserId DESC
ʹÓÃRow_numberµÄ´æ´¢¹ý³Ì
CREATE proc [dbo].[test_PageByRowNumber]
(
@pageIndex int,
@pageSize int
)
AS
DECLARE @startRow int, @endRow int
Set @startRow = (@pageIndex - 1) * @pageSize +1
SET @endRow = @startRow + @pageSize -1
SELECT*
from (
SELECT *,ROW_NUMBER() OVER (ORDER BY UserId DESC) AS RowNumber
from test ) T
WHERE T.RowNumber BETWEEN @startRow AND @endRow
²âÊԺͽá¹û
SET STATISTICS io ON
SET STATISTICS time ON
go
EXEC test_PageByRowNumber 1000,50¡¡--RowNumber´æ´¢¹ý³Ì
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF
/*½á¹û·ÖÎö*/
SQL Server ·ÖÎöºÍ±àÒëʱ¼ä:
CPU ʱ¼ä = 0 ºÁÃ룬ռÓÃʱ¼ä = 1 ºÁÃë¡£
SQL Server ·ÖÎöºÍ±àÒëʱ¼ä:
CPU ʱ¼ä = 0 ºÁÃ룬ռÓÃʱ¼ä = 1 ºÁÃë¡£
SQL Server Ö´ÐÐʱ¼ä:
CPU ʱ¼ä = 0 ºÁÃ룬ռÓÃʱ¼ä = 1 ºÁÃë¡£
SQL Server Ö´ÐÐʱ¼ä:
CPU ʱ¼ä = 0 ºÁÃ룬ռÓÃʱ¼ä = 1 ºÁÃë¡£
(50 ÐÐÊÜÓ°Ïì)
±í '
Ïà¹ØÎĵµ£º
TABLE MASTER ×Ö¶Î ID DETAIL.....
TABLE BIZ ×Ö¶Î SYS_ID CODE_ID .......
²éѯʱÐèÒªµÄÊÇIDµÄÃèÊö
1,SELECT A.SYS_ID,A.CODE_ID,B.DETAIL,C.DETAIL...... from BIZ A,MASTER B,MASTER C WHERE A.SYS_ID=B.ID AND A.CODE_ID=C.ID
2,SELECT SYS_ID,(SELECT DETAIL from MASTER ......
LINQ to sqlËäÈ»½«Êý¾Ý¿â²Ù×÷ºÍÒµÎñÂß¼¸ôÀ뿪À´£¬Ê¹¿ª·¢ÈËÔ±Äܹ»Ê¹Óõ¥Ò»µÄÓïÑÔºÍ֪ʶÄܹ»·½±ãµÄ²Ù×÷Êý¾Ý¿â²¢´¦ÀíÒµÎñÂß¼¡£µ«ÊÇÕâ±Ï¾¹ÊÇ΢ÈíO/R½â¾ö·½°¸µÄµÚÒ»¸ö°æ±¾£¬Ïà±ÈÏà¶Ô³ÉÊìµÄDataSetÊý¾Ý¼¯½â¾ö·½°¸À´Ëµ£¬ÎÒÃÇ»¹ÊÇ¿ÉÒÔ¿´µ½Ò»Ð©²»×ã¡£
¡¡¡¡Ê×ÏÈ£¬ÎÒÃÇ×¢Òâµ½ËùÓеÄÊý¾ÝʵÌ岢ûÓдÓÒ»¸ö»ùÀàÖÐÅÉÉú£¬ÕâʹµÃ¸ø¿ª·¢Í¨Ó ......
selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1;
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®º ......
----start
¶¯Ì¬SQLÊÇÔÚ³ÌÐòÔËÐÐʱ¹¹ÔìµÄ£¬ÒªÖ´Ðе¥ÌõSQL£¬Ê¹ÓÃEXECUTE IMMEDATE Óï¾ä£»µ±ÅúÁ¿Ö´ÐÐSQLʱ£¬ÏÈʹÓÃPREPARE Óï¾ä¹¹ÔìSQL£¬È»ºóʹÓÃEXECUTE Óï¾äÖ´ÐС£
Ò»£ºPrepareÓï¾ä£ºÓÃÀ´¹¹ÔìÅúÁ¿SQL
Óï·¨£º
PREPARE <sql-statement> [OUTPUT] INTO <result> [INPUT INTO] <input> ......
sql serverºÍoracleÐÐתÁеÄÒ»ÖÖµäÐÍ·½·¨
ǰÑÔ£ºÍøÉÏÓв»ÉÙÎÄÕÂÊǽ²ÐÐתÁе쬵«ÊǴ󲿷ֶ¼ÊÇÖ±½ÓÌù´úÂ룬ºöÊÓÁËÖмä¹ý³Ì£¬±¾ÈË×Ô¼ºË¼¿¼ÁËÏÂΪʲôҪÕâÑùʵÏÖ£¬²¢ÇÒ×öÁËÈçϵıʼǣ¬¶ÔÓÐЩ¶®µÄÈËÀ´Ëµ¿ÉÄÜûÓмÛÖµ£¬Ï£Íû¶Ô»¹²»¶®µÄÈËÓÐÒ»µã½è¼øÒâÒå¡£
¶ÔÓÚÓÐЩҵÎñÀ´Ëµ£¬Êý¾ÝÔÚ±íÖеĴ洢ºÍÆä×îÖÕµÄGrid±íÏÖÇ¡ºÃÏ൱ÓÚ°ÑÔ ......