SQL SERVER 2005·ÖÒ³´æ´¢¹ý³Ì
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PE011_Page]
@TableName varchar(50), --±íÃû
@Fields varchar(5000) = '*', --×Ö¶ÎÃû(È«²¿×Ö¶ÎΪ*)
@OrderField varchar(5000), --ÅÅÐò×Ö¶Î(±ØÐë!Ö§³Ö¶à×Ö¶Î)
@sqlWhere varchar(5000) = Null,--Ìõ¼þÓï¾ä(²»ÓüÓwhere)
@pageSize int, --ÿҳ¶àÉÙÌõ¼Ç¼
@pageIndex int = 1 , --Ö¸¶¨µ±Ç°ÎªµÚ¼¸Ò³
@totalRecord int = 0 output,
@TotalPage int output --·µ»Ø×ÜÒ³Êý
AS
BEGIN
Begin Tran --¿ªÊ¼ÊÂÎñ
Declare @sql nvarchar(4000);
if @totalRecord<=0 begin
--¼ÆËã×ܼǼÊý
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' with(nolock) where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--¼ÆËã×ܼǼÊý
end
--¼ÆËã×ÜÒ³Êý
select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if (@SqlWhere='' or @sqlWhere=NULL)
set @sql = 'Select * from (select ROW_NUMBER
Ïà¹ØÎĵµ£º
Êý¾Ý¿â¿ª·¢¹ý³ÌÖУ¬µ±Äã¼ìË÷µÄÊý¾ÝÖ»ÊÇÒ»Ìõ¼Ç¼ʱ£¬ÄãËù±àдµÄÊÂÎñÓï¾ä´úÂëÍùÍùʹÓÃSELECT INSERT Óï¾ä¡£µ«ÊÇÎÒÃdz£³£»áÓöµ½ÕâÑùÇé¿ö£¬¼´´Óijһ½á¹û¼¯ÖÐÖðÒ»µØ¶ÁȡһÌõ¼Ç¼¡£ÄÇôÈçºÎ½â¾öÕâÖÖÎÊÌâÄØ£¿ÓαêΪÎÒÃÇÌṩÁËÒ»ÖÖ¼«ÎªÓÅÐãµÄ½â¾ö·½°¸¡£
1.1 ÓαêºÍÓαêµÄÓŵã
& ......
·ÖÀàͳ¼Æ×ÜÊý²¢ÅÅÐò¶à±íÁªºÏ²éѯµÄÁ½ÖÖ·½·¨
Àý:
²éѯµØÇø±íÖи÷Ê¡ÏÂÃæ³ÇÊÐ×ÜÊý,²¢¶Ô²éѯ½á¹ûȡǰʮÃû
·¨Ò»:
select b.id,a.[name],b.counts from n_area a,(select top 10 parent_id as id,sum(parent_id) as counts from n_area where parent_id<>0 group by parent_id order by sum(parent_id) desc
) b where ......
¹¤¾ß£º
Sybase PowerDesigner 12.5
Microsoft SQL Server 2005
µÚÒ»²½£º¸ÅÒªÉè¼Æ
´ò¿ªPowerDesignerÈí¼þ£¬Éè¼Æ“¸ÅÄîÊý¾ÝÄ£ÐÍ”(Conceptual Data Model)£º
ͼ1
Éè¼Æ±í½á¹¹£º
ͼ2
ÔÚÉè¼ÆÊôÐÔ(×Ö¶Î)µÄʱºò£¬Èý¸ö×Öĸ(M¡¢P¡¢D)·Ö±ð±íʾ£º
M£ºÊÇ·ñΪ¿Õ;(√±íʾ²»ÔÊÐíΪ¿Õ)
P£ºÊÇ·ñ뻅 ......
1.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖжÔ×ֶνøÐÐ null ÖµÅжϣ¬·ñÔò½«µ¼ÖÂÒýÇæ·ÅÆúʹÓÃË÷Òý¶ø½øÐÐÈ«±íɨÃ裬È磺
select id from t where num is null
¿ÉÒÔÔÚnumÉÏÉèÖÃĬÈÏÖµ0£¬È·±£±íÖÐnumÁÐûÓÐnullÖµ£¬È»ºóÕâÑù²éѯ£º
select id from t where num=0
2.Ó¦¾¡Á¿±ÜÃâÔÚ where ×Ó¾äÖÐʹÓÃ!=»ò<>²Ù×÷·û£¬·ñÔò½«ÒýÇæ·ÅÆúÊ ......
1. ¸´ÖƱí½á¹¹
Sql´úÂë
1. select * into B from A where 1=0;
select * into B from A where 1=0;
2.¸´ÖƱí¼Ç¼ ¸´ÖÆÄ³Ð©×Ö¶Î
Sql´úÂë
1. insert into B(a, b, c) select d, e, f from A;
insert into B(a, b, c) select d, e, f from A;
¸´Ö ......