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

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 ÐÐÊÜÓ°Ïì)
±í '


Ïà¹ØÎĵµ£º

SQLÓï¾ä ÁªºÏ²éѯ»¹ÊÇÔÚ²éѯ½á¹ûÖÐǶÌ×

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 ......

SQL¼¶Áª¸üкͼ¶ÁªÉ¾³ý

alter table ±íÃû
add constraint Ô¼ÊøÃû
foreign key(×Ö¶ÎÃû) references Ö÷±íÃû(×Ö¶ÎÃû)
on delete cascade
Óï·¨£º
Foreign Key
(column[,...n])
references referenced_table_name[(ref_column[,...n])]
[on delete cascade]
[on update cascade]
×¢ÊÍ£º
column:ÁÐÃû
referenced_table_name:Íâ¼ü²Î¿¼µÄÖ÷¼ü± ......

SQL ServerÈçºÎÌá¸ßÊý¾Ý¿â»¹Ô­µÄËÙ¶È

Ó°ÏìÊý¾Ý¿â»¹Ô­ËٶȵÄÒòËØºÍÓ°ÏìÊý¾Ý¿â±¸·ÝËٶȵÄÒòËØÏàͬ¡£³ý´ËÖ®Í⣬¼ÙÈçÄãʹÓÃSQL Server 2005µÄ»°£¬Ä㻹¿ÉÒÔÆô¶¯ÁíÍâÒ»¸öÓÅ»¯ÈÎÎñÀ´»¹Ô­µ±Ç°²»´æÔÚµÄÊý¾Ý¿â£¬ÔËÐл·¾³ÎªWindows XP£¬Windows 2003 Server »ò¸üа汾¡£
Perform Volume Maintenance Tasks
µ±Ä㻹ԭһ¸öеÄÍêÕûÊý¾Ý¿âÊÇ£¬SQL Server¶Á±¸·ÝÎļþÍ·£¬È»ºó´ ......

ѧϰSQLÓï¾äÖ®SQLÓï¾ä´óÈ«

¡¡--Óï ¾ä ¹¦ ÄÜ¡¡¡¡--Êý¾Ý²Ù×÷¡¡¡¡SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁС¡¡¡INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐС¡¡¡DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐС¡¡¡UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý¡¡¡¡--Êý¾Ý¶¨Òå¡¡¡¡CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í¡¡¡¡DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í¡¡¡¡ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹¡¡¡¡CREATE VI ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ