Contents

1 覦覯1
2 覦覯2
3
4 SQL Server 2005 CTE
5 SQL Server 2008 Hierarchyid
6 谿瑚襭


1 覦覯1 #

螻豸糾規譟磯ゼ 貊襦 企 蟆企. 螻豸糾規譟磯ゼ 貊襦 企 蟆曙 貊 螳 襷れ 譴. 譬 蟆 豌 螳螳螳 覯 1螳 蠍襦 襷れ 讌 蟆企. 襷 1螳 覓語襦 覈 讌 る 襴 襯 襴覃 蟆企. 企 覯 1蠍碁 螳 覯 覿蟲螻 覓語螳 襴る 螳 覯 企 貉殊 襷れ伎語 .

recursive01.jpg
CREATE FUNCTION FN_SUM_AMT (@CODE VARCHAR(10))
RETURNS INT
AS
BEGIN
   DECLARE @SUM INT
   SELECT @SUM = SUM(AMT)
   FROM T_AMT
   WHERE CODE LIKE @CODE + '%'
   RETURN(@SUM)
END
GO
 
SELECT CODE, NAME, TEMPDB.DBO.FN_SUM_AMT(CODE) AS AMT
FROM T_CODE
 
--
SELECT  A.DEPT, A.CODE, MIN(A.NAME) NAME, B.YMD, SUM(B.AMT) AMT
FROM    T_CODE  A, T_AMT   B
WHERE   A.DEPT=B.DEPT
AND     B.CODE LIKE A.CODE+'%'
GROUP BY A.DEPT, A.CODE, B.YMD

2 覦覯2 #

一危一 郁屋 襯 伎 覦覯螻 Join 牛 郁屋 ′語るゼ . 貊襦 覃 襭襯 讌 レ 朱 覲蟆曙 麹 豬渚る 曙 . 轟 襴 殊語 蟯螻 企 襴襦 襴酔 燕 蟆螻 蠏 企.

--蟯螻 襦
CREATE PROC 襦覈
AS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE xtype = 'U' AND name LIKE '%#企覈%')
   DROP TABLE #企覈
 
CREATE TABLE #企覈
(
   貉 一危壱 ....
)
 
DECLARE @i AS INT, @path AS VARCHAR(50)
SET @i = 0
SET @path = ','
 
INSERT INTO #企覈
SELECT 貉朱Μろ, @i, ',' + 貉朱 + ',' FROM れ企覈
WHERE 覿覈貉朱 IS NULL -- 覿覈貉 = 覿覈貉手
 
WHILE @@ROWCOUNT > 0 BEGIN
   SET @i = @i + 1
   INSERT INTO #企覈
   SELECT れ企.貉朱 .....  , @i, #企.path + れ企.貉 + ','
   FROM れ企 INNER JOIN #企
   ON れ企.覿覈貉 = #企.貉
   AND #企.i = @i - 1
END

SELECT 
	REPLICATE(' ', i) + CAST(i + 1 AS VARCHAR) AS 'LEVEL'
,	REPLICATE('->', i) + 貉手 AS 'LEVEL_NAME'
,	i
,	path
,	貉
,	覿覈貉
FROM #企
ORDER BY path

3 #

--DROP TABLE employees
 
CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid)
      )
GO
 
INSERT INTO employees VALUES ('*', '覩', null)
INSERT INTO employees VALUES ('1', '', '*')
INSERT INTO employees VALUES ('2', '伎', '1')
INSERT INTO employees VALUES ('3', '伎', '1')
INSERT INTO employees VALUES ('4', '伎', '1')
INSERT INTO employees VALUES ('5', '覿', '2')
INSERT INTO employees VALUES ('6', '覿', '2')
INSERT INTO employees VALUES ('7', '覿', '2')
INSERT INTO employees VALUES ('8', '螻殊', '3')
INSERT INTO employees VALUES ('9', '螻殊', '3')
INSERT INTO employees VALUES ('10', '覿', '4')
INSERT INTO employees VALUES ('11', '', '7')
INSERT INTO employees VALUES ('12', '', '7')
INSERT INTO employees VALUES ('13', '', '7')
INSERT INTO employees VALUES ('14', '', '7')
INSERT INTO employees VALUES ('111', '', '*')
INSERT INTO employees VALUES ('222', '伎', '111')
INSERT INTO employees VALUES ('333', '覿', '222')
INSERT INTO employees VALUES ('444', '螻殊', '333')
INSERT INTO employees VALUES ('555', '襴', '444')
INSERT INTO employees VALUES ('666', '', '555')
SELECT * FROM employees
GO
 
--drop index employees.idx_emp_mgr
create index idx_emp_mgr
on employees(mgrid)
GO
 
ALTER PROC proc_test
AS
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE xtype = 'U' AND name LIKE '%#emp%')
   DROP TABLE #emp
 
CREATE TABLE #emp (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid),
      i int,
      path varchar(50)
      )
 
DECLARE @i AS INT, @path AS VARCHAR(50)
SET @i = 0
SET @path = ','
 
INSERT INTO #emp
SELECT empid, empname, mgrid, @i, ',' + empid + ',' FROM employees
WHERE mgrid = '*' -- 覿覈貉 = 覿覈貉手
 
WHILE @@ROWCOUNT > 0 BEGIN
   SET @i = @i + 1
   INSERT INTO #emp
   SELECT a.empid, a.empname, a.mgrid , @i, b.path + a.empid + ','
   FROM employees a INNER JOIN #emp b
   ON a.mgrid = b.empid
   AND b.i = @i - 1
END
 
SELECT REPLICATE(' ', i) + CAST(i + 1 AS VARCHAR) 'LEVEL', 
             REPLICATE('->', i) + empname 'LEVEL_NAME', 
             i, path, empid, mgrid
FROM #emp
ORDER BY  path
GO
 
exec proc_test

れ 殊 襷 蟯螻 覈語 企企. 蠏碁Μ 譬讌襷 企襦 谿瑚襷蠍 覦. Mgrid螳 empid襯 谿語^螻 覈旧企.
CREATE TABLE employees (empid nchar(5) PRIMARY KEY, 
      empname nvarchar(50), 
      mgrid nchar(5) REFERENCES employees(empid), 
      title nvarchar(30)
      )
GO
 
CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given 
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key, 
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee 
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees 
   WHERE empid = @InEmpId 
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be 
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END
   
   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title 
   FROM @reports
   RETURN
END
GO
 
-- Example invocation
SELECT * 
FROM fn_FindReports('11234')
GO

4 SQL Server 2005 CTE #

SQL Server 2005覯 蟯螻襯 願屋 覦覯朱 CTE[1]企 覲伎. 誤 蟆 襷 谿瑚蠍 覦. れ 蠏 覯企.
USE AdventureWorks
GO

WITH T_Recursive(ManagerID, EmployeeID, Title, Level)
AS
(
	--Root.. 
	SELECT ManagerID, EmployeeID, Title, 0 AS Level
	FROM HumanResources.Employee
	WHERE ManagerID IS NULL --Root 譟郁唄

	UNION ALL

	SELECT A.ManagerID, A.EmployeeID, A.Title, B.Level + 1 AS Level
	FROM HumanResources.Employee A INNER JOIN T_Recursive B --蠏貎朱Μ
	ON A.ManagerID = B.EmployeeID
)

SELECT 
	ManagerID
,	CAST(REPLICATE(' --> ', Level) + CAST(EmployeeID AS VARCHAR) AS CHAR(25)) AS EmployeeID
,	Title
,	Level
FROM T_Recursive A
ORDER BY Level

--蟆郁骸
ManagerID   EmployeeID                Title                                              Level
----------- ------------------------- -------------------------------------------------- -----------
NULL        109                       Chief Executive Officer                            0
109          --> 6                    Marketing Manager                                  1
109          --> 12                   Vice President of Engineering                      1
109          --> 42                   Information Services Manager                       1
109          --> 140                  Chief Financial Officer                            1
109          --> 148                  Vice President of Production                       1
109          --> 273                  Vice President of Sales                            1
273          -->  --> 268             North American Sales Manager                       2
273          -->  --> 284             European Sales Manager                             2
273          -->  --> 288             Pacific Sales Manager                              2
148          -->  --> 21              Production Control Manager                         2
148          -->  --> 44              Master Scheduler                                   2
148          -->  --> 200             Quality Assurance Manager                          2
148          -->  --> 218             Facilities Manager                                 2
140          -->  --> 30              Human Resources Manager                            2
140          -->  --> 71              Finance Manager                                    2
140          -->  --> 103             Assistant to the Chief Financial Officer           2
140          -->  --> 139             Accounts Manager                                   2
.
.
.
274          -->  -->  -->  --> 261   Buyer                                              4
274          -->  -->  -->  --> 264   Purchasing Assistant                               4
274          -->  -->  -->  --> 266   Purchasing Assistant                               4
263          -->  -->  -->  --> 5     Tool Designer                                      4
263          -->  -->  -->  --> 265   Tool Designer                                      4
158          -->  -->  -->  --> 79    Research and Development Engineer                  4
158          -->  -->  -->  --> 114   Research and Development Engineer                  4
158          -->  -->  -->  --> 217   Research and Development Manager                   4

(290 row(s) affected)
(1 row(s) affected)

るジ
CREATE View [Common].[vDim_Item]
AS
WITH vRecursive(ItemKey, ItemNM, FullPath, OrderPath, ParentKey, ParentNM, Price, ContentsNo, ContentsNM, MenuNo, MenuNM, MixBillNo, VerNo, SiteCD, Level, LeafLevelYN)
AS
(
	SELECT 
		ItemKey
	,	ItemNM
	,	CONVERT(varchar(500), ItemNM) FullPath
	,	CONVERT(varchar(500), ItemKey) OrderPath
	,	ParentKey
	,	CONVERT(varchar(50), NULL) ParentNM
	,	Price
	,	ContentsNo
	,	ContentsNM
	,	MenuNo
	,	MenuNM
	,	MixBillNo
	,	VerNo
	,	SiteCD
	,	0 Level
	,	LeafLevelYN
	FROM Common.Dim_Item
	WHERE ParentKey IS NULL 
	--WHERE ItemKey = 1
	
	UNION ALL

	SELECT 
		A.ItemKey
	,	A.ItemNM
	,	CONVERT(varchar(500), B.FullPath + '/' + A.ItemNM) FullPath
	,	CONVERT(varchar(500), CONVERT(varchar, B.OrderPath) + CONVERT(varchar, A.ItemKey)) OrderPath
	,	A.ParentKey
	,	B.ItemNM ParentNM
	,	A.Price
	,	A.ContentsNo
	,	A.ContentsNM
	,	A.MenuNo
	,	A.MenuNM
	,	A.MixBillNo
	,	A.VerNo
	,	A.SiteCD
	,	B.Level + 1 Level
	,	A.LeafLevelYN
	FROM Common.Dim_Item A INNER JOIN vRecursive B
	ON A.ParentKey = B.ItemKey
	where Level < 10
)
SELECT 
	ParentKey
,	ParentNM
,	ItemKey
,	ISNULL(REPLICATE('   ', Level-1), '') + CASE WHEN ParentKey IS NOT NULL THEN '' ELSE '' END + CAST(ItemKey AS VARCHAR) AS Level_Tree
,	ISNULL(REPLICATE('   ', Level-1), '') + CASE WHEN ParentKey IS NOT NULL THEN '' ELSE '' END + ItemNM ItemNM_Tree
,	ItemNM
,	FullPath
,	OrderPath
,	Price
,	ContentsNo
,	ContentsNM
,	MenuNo
,	MenuNM
,	MixBillNo
,	VerNo
,	SiteCD
,	Level
,	LeafLevelYN
FROM vRecursive A
--OPTION (MAXRECURSION 0);

5 SQL Server 2008 Hierarchyid #

SQL Server 2008 蟯螻 覦 螻豸糾規譟一 企朱 Hierarchyid 一危壱 覲伎. Hierarchyid れ螻 螳 轟 螳讌. 襷 れ .

  • 螻豸旧 Data type
    • 譟一蟲譟

    • 襦 ろ 讌
    • 語 伎 覿襯
    • 伎 螳 襷 蠏碁


れ 企.

USE AdventureWorks
GO

--企
CREATE TABLE HumanResources.EmployeeOrg
(
   OrgNode hierarchyid PRIMARY KEY CLUSTERED, --蠍一朱!
   OrgLevel AS OrgNode.GetLevel(), --蠍一朱!
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
GO

--碁煙れ(願鍵覦碁煙れ)
CREATE UNIQUE INDEX EmployeeOrgNc1 
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;
GO

--襭語曙
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;
GO

--蟆1
SELECT * FROM HumanResources.EmployeeOrg
recursive02.jpg

  1. --蟆2
  2. SELECT
  3. OrgNode.ToString() AS Text_OrgNode
  4. , OrgNode
  5. , OrgLevel
  6. , EmployeeID
  7. , EmpName
  8. , Title
  9. FROM HumanResources.EmployeeOrg
  10.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari
recursive03.jpg

--覿讌 曙(GetDescendant()覃)
recursive04.jpg

  1. DECLARE @Manager HIERARCHYID
  2. SELECT @Manager = HIERARCHYID::GetRoot()
  3. FROM HumanResources.EmployeeOrg ;
  4.  
  5. INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
  6. VALUES
  7. (@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ;
  8.  
  9. --蟆
  10. SELECT
  11. OrgNode.ToString() AS Text_OrgNode
  12. , OrgNode
  13. , OrgLevel
  14. , EmployeeID
  15. , EmpName
  16. , Title
  17. FROM HumanResources.EmployeeOrg
  18.  
* IE拭辞 社什 差紫獣 匝郊嘩 照喫. MS-Word 去拭 細食 隔生室推. 匝郊嘩 鞠澗 崎虞酔煽: Chrome, Opera, Safari
recursive05.jpg

--蠏谿朱襦燕曙
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) 
AS 
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode 
   FROM HumanResources.EmployeeOrg 
   WHERE EmployeeID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM HumanResources.EmployeeOrg 
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title)
   COMMIT
END ;
GO

EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ;
EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ;
EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ;
EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;
EXEC AddEmp 6, 120, 'yasi', 'Marketing Specialist' ;

--蟆
SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	OrgNode
,	OrgLevel
,	EmployeeID
,	EmpName
,	Title 
FROM HumanResources.EmployeeOrg
recursive06.jpg

SELECT 
	OrgNode.ToString() AS Text_OrgNode
,	hierarchyid::GetRoot() AS Text_Root
,	OrgNode.GetAncestor(1) AS Text_Ancestor
,	(SELECT EmpName FROM HumanResources.EmployeeOrg B
	WHERE B.OrgNode = A.OrgNode.GetDescendant(0x78, NULL)) 
FROM HumanResources.EmployeeOrg A
WHERE EmpName = 'David'
recursive07.jpg

6 谿瑚襭 #