[ת]Éú³ÉÎÞ¼¶Ê÷(sqlº¯Êý)
--´¦ÀíʾÀý
--ʾÀýÊý¾Ý
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4
go
--´´½¨´¦ÀíµÄº¯Êý
create function f_id()
returns @re table(id int,level int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select id,@l,right(10000+id,4)
from tb where ParentID=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,b.sid+','+right(10000+a.id,4)
from tb a,@re b
where a.ParentID=b.id and b.level=@l-1
end
return
end
go
--µ÷Óú¯ÊýʵÏÖ²éѯ
select a.*,´øËõ½øµÄName=space(b.level*4)+a.Name
from tb a,f_id() b
where a.id=b.id
order by b.sid
go
--ɾ³ý²âÊÔ
drop table tb
drop function f_
[ת]http://www.cnblogs.com/catxp/articles/381747.html
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Ä㶼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâƪÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ¡£Õ ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE data ......
ÏÖÏó£º
ÔÚʹÓÃMicrosoft SQL Server 2005ʱ£¬Òª´´½¨Ò»¸öµÇ¼Ãû£¬²¢Îª¸ÃµÇ¼Ãû¹ØÁªÁËÒ»¸öÊý¾Ý¿â£¬µ«ÊÇÔÚÑ¡Ôñ“°²È«¶ÔÏó”Ñ¡Ïîʱ£¬È´³öÏÖÁËÈçÌâËùʾµÄ´íÎó¡£ÆäËûÐÅÏ¢ÏÔʾΪ£ºÖ´ÐÐTransact-SQLÓï¾ä»òÅú´¦Àíʱ·¢ÉúÁËÒì³£(Microsoft.SqlServer.ConnectionInfo)¡£ÎÞ·¨½â¾ö ......
Êý¾Ý¿âÐÔÄÜÓÅ»¯Éæ¼°µ½ºÜ¶à·½Ã棬ÔÚÊý¾Ý¿â¿ª·¢Ê±¿ÉÒÔͨ¹ýһЩ»ù±¾µÄÓÅ»¯¼¼ÇÉÌá¸ßÊý¾Ý¿âµÄÐÔÄÜ£º
1£®ÔÔòÉÏΪ´´½¨µÄÿ¸ö±í¶¼½¨Á¢Ò»¸öÖ÷¼ü,Ö÷¼üΨһ±êʶijһÐмǼ£¬ÓÃÓÚÇ¿ÖƱíµÄʵÌåÍêÕûÐÔ¡£SQL Server 2005 Database Engine ½«Í¨¹ýΪÖ÷¼üÁд´½¨Î¨Ò»Ë÷ÒýÀ´Ç¿ÖÆÊý¾ÝµÄΨһÐÔ¡£²éѯÖÐʹÓÃÖ÷¼üʱ£¬´ËË÷Òý»¹¿ÉÓÃÀ´¶ÔÊý¾Ý½øÐпì ......