[Sql Server2008]Ê÷½á¹¹µÄµÝ¹éËã·¨
±¾ÎÄÖ÷Òª½²ÊöÈý¸öÄÚÈÝ£º
1.ÈçºÎ´´½¨hierarychyidµÄ±í£¬²åÈëÊý¾Ý¼°»ù±¾µÝ¹é²éѯ¡£
2.½éÉÜhierarchyidµÄ10ÖÖרÓк¯Êý¡£
3.½éÉÜhierarchyidÌØÓеÄÉî¶ÈÓÅÏÈË÷Òý(Depth-First Indexing)ºÍ¹ã¶ÈÓÅÏÈË÷Òý(Breadth-First Indexing)
ÔÚÉÏÒ»½ÚÖÐ
http://blog.csdn.net/tjvictor/archive/2009/07/30/4395677.aspx
ÎÒÃÇÒѾÑÝʾÁËÈçºÎÔÚSQL ServerÖÐͨ¹ýÖ÷¼üºÍÍâ¼üÀ´´æ´¢ÈçÏÂͼËùʾµÄÊ÷ÐͽṹÊý¾Ý
ËäȻͨ¹ýÖ÷¼üºÍÍâ¼üµÄÏ໥´îÅä¿ÉÒÔÂú×ãÎÒÃǵIJéѯ¡¢´æ´¢ÐèÇ󣬵«ÊÇÕâÖÖ·½Ê½²¢²»Ò×ÓÚ¹ÜÀíºÍά»¤£¬ÐÒÔ˵ÄÊÇ£¬ÔÚSQL Server 2008ÖÐÌṩÁËÒ»ÖÖеÄÊý¾ÝÀàÐÍhierarchyidºÍÏà¹ØµÄ²Ù×÷·½·¨À´´æ´¢ºÍ²éѯÕâÖÖÊ÷ÐͲã´Î¹ØϵÊý¾Ý¡£
Ê×ÏÈ´´½¨Êý¾Ý±í:
create database TestDb
go
use TestDb
go
Create table EmployeeTreeTable
(
NodeId hierarchyid PRIMARY KEY,
NodeLevel AS NodeId.GetLevel(),
EmployeeId int UNIQUE NOT NULL,
EmployeeName nvarchar(32) NOT NULL,
)
NodeIdÊǼǼÊ÷ÐͲã´ÎµÄId£¬ÊÇhierarchyidÀàÐÍ¡£NodeLevelÊǸö¼ÆËãÁУ¬ÓÃÓÚ´æ´¢µ±Ç°Ê÷ÊÇÉî¶ÈÖµ£¬¸ù½ÚµãΪ0¡£¹ØÓÚNodeId.GetLevel()·½·¨½«ÔÚÏÂÃæÕ½ÚÖÐÏêϸ½éÉÜ¡£
°´ÕÕÉÏͼËùʾµÄ²ã´Î¹ØϵΪ±í²åÈëÊý¾Ý£º
--²åÈëÊý¾Ý
declare @DepthNode hierarchyid;--Éî¶ÈId
declare @BreadthNode hierarchyid;--¹ã¶ÈId
--²åÈë¸ù½Úµã
insert into EmployeeTreeTable values(hierarchyid::GetRoot(),1,'ÏîÄ¿¾Àí')
--¼ÆËãÉî¶È²¢²åÈë×Ó½Úµã2
select @DepthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 1;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(null,null),2,'¼¼Êõ¾Àí');
--¼ÆËã½Úµã2¹ã¶È£¬ÔÚ½Úµã2Óұ߲åÈë½Úµã3
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 2;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),3,'²úÆ·¾Àí');
--¼ÆËã½Úµã3¹ã¶È£¬ÔÚ½Úµã3Óұ߲åÈë½Úµã4
select @BreadthNode = NodeId from EmployeeTreeTable where [EmployeeId] = 3;
insert into EmployeeTreeTable values(@DepthNode.GetDescendant(@BreadthNode,null),4,'²âÊÔ¾Àí');
--¼ÆËã½Úµã2Éî¶È²¢²åÈë×Ó½Úµã5
select @DepthNode = NodeId from EmployeeTreeTa
Ïà¹ØÎĵµ£º
#region µÃµ½ËùÓб¾µØÍøÂçÖпÉʹÓõÄSQL·þÎñÆ÷Áбí
/// <summary>
/// µÃµ½ËùÓб¾µØÍøÂçÖпÉʹÓõÄSQL·þÎñÆ÷Áбí
/// </summary>
/// <param name="p_strServerList">·þÎñÆ÷Áбí</param& ......
ѧ»áÊý¾Ý¿âÊǺÜʵÓÃD~~¼Ç¼һЩ³£ÓõÄsqlÓï¾ä...ÓÐÈëÃÅÓÐÌá¸ßÓмû¶¼Ã»¼û¹ýµÄ...ºÃÈ«...ÊÕ²ØÏÂ...
Æäʵһ°ãÓõľÍÊDzéѯ,²åÈë,ɾ³ýµÈÓï¾ä¶øÒÑ....µ«Ñ§Ñ§´æ´¢¹ý³ÌÊǺÃÊÂ...ÒÔºóÊý¾Ý·½ÃæµÄ¶«Î÷¾Í²»ÓÃÔÚ³ÌÐòÀï¸ãà¶..¶øÇÒ³ÌÐòÓëÊý¾Ý¿âÖ»ÒªÒ»¸öÀ´»ØͨѶ¾Í¿ÉÒԸ㶨ËùÓÐÊý¾ÝµÄ²Ù×÷....
Ò»¡¢»ù´¡
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
Create DAT ......
Ò»¡¢¼òµ¥ÊµÀý
1.Ê×ÏÈÀ´Ò»¸ö×î¼òµ¥µÄ´æ´¢¹ý³Ì°É
CREATE PROCEDURE dbo.testProcedure_AX
AS
select userID from USERS order by userid desc
×¢:dbo.testProcedure_AXÊÇÄã´´½¨µÄ´æ´¢¹ý³ÌÃû,¿ÉÒÔ¸ÄΪ:AXzhzµÈ,±ð¸ú¹Ø¼ü×Ö³åÍ»¾ÍÐÐÁË,ASÏÂÃæ¾ÍÊÇÒ»ÌõSQLÓï¾ä.
2.ÈçºÎÔÚASP.NETÖе÷ÓÃÕâ¸ö´æ´¢¹ý³Ì?
&n ......
1. GROUP BY ÊÇ·Ö×é²éѯ, Ò»°ã GROUP BY ÊǺ;ۺϺ¯ÊýÅäºÏʹÓÃ
group by ÓÐÒ»¸öÔÔò,¾ÍÊÇ select ºóÃæµÄËùÓÐÁÐÖÐ,ûÓÐʹÓþۺϺ¯ÊýµÄÁÐ,±ØÐë³öÏÖÔÚ group by ºóÃ棨ÖØÒª£©
ÀýÈç,ÓÐÈçÏÂÊý¾Ý¿â±í£º
A B
1 abc
1 bcd
1 asdfg
Èç¹ûÓÐÈ ......