SQL Server 2008 中的hierarchyid类型应用
SQL Server 2008的一个重要新增特性。主要解决的问题是拥有层次关系的表格。例如我们日常生活中用到最多的组织结构图。我们一般会用一个Employees表保存员工数据,而每个员工则又可能会有相应的上级。以前要得到某个员工的所有上级,或者所有下级,通常所采
SQL Server 2008的一个重要新增特性。主要解决的问题是拥有层次关系的表格。例如我们日常生活中用到最多的组织结构图。我们一般会用一个Employees表保存员工数据,而每个员工则又可能会有相应的上级。以前要得到某个员工的所有上级,或者所有下级,通常所采取的方法都是递归。SQL Server 2005开始支持的CTE从一定程序上方便了该工作的实现。
但SQL 2008的hierarchyid让这个工作更加简化和直接。而该类型其实是一个CLR自定义数据类型。
一般我们使用的时候,如下面的例子
--创建表
CREATE TABLE Employees
(
Org_Id hierarchyid NOT NULL,
EmployeeId INT NOT NULL,
EmployeeName VARCHAR(50) NOT NULL,
Title VARCHAR(50) NOT NULL
)
GO
--插入一些员工,注意第一个列的格式,必须用/开始和结束。这是一个路径的符号。这是一个关键
INSERT INTO dbo.Employees VALUES('http://www.3lian.com/',10000,'陈希章','CEO');
INSERT INTO dbo.Employees VALUES('/1/',10001,'张三','CTO');
INSERT INTO dbo.Employees VALUES('/2/',10002,'李四','CFO');
INSERT INTO dbo.Employees VALUES('/1/1/',10003,'王五','IT Manager');
INSERT INTO dbo.Employees VALUES('/1/2/',10004,'赵六','Manager');
INSERT INTO dbo.Employees VALUES('/1/1/1/',10005,'洪七','Employee');
--查看所有的员工
SELECT * FROM dbo.Employees
--查看所有的员工及其级别
SELECT *,Org_Id.GetLevel() AS Level FROM Employees
--查看陈希章的所有下属
DECLARE @BOSS hierarchyid
SELECT @BOSS=Org_Id FROM Employees WHERE EmployeeID=10000
SELECT *,Org_Id.GetLevel()AS Level FROM Employees WHERE Org_Id.IsDescendantOf(@BOSS)=1
--查看赵六及其所有上级
DECLARE @Employee hierarchyid
SELECT @Employee=Org_Id FROM Employees WHERE EmployeeID=10004
SELECT *,Org_Id.GetLevel()AS Level FROM Employees WHERE @Employee.IsDescendantOf(Org_Id)=1
与hierarchyid有关的一些函数主要有
GetAncestor :取得某一个级别的祖先
GetDescendant :取得某一个级别的子代
GetLevel :取得级别
GetRoot :取得根
IsDescendantOf :判断某个节点是否为某个节点的子代
Parse :将字符串转换为hierarchyid。该字符串的格式通常都是/1/这样的
Read :
Read 从传入的 BinaryReader 读取 SqlHierarchyId 的二进制表示形式,并将 SqlHierarchyId 对象设置为该值。不能使用 Transact-SQL 调用 Read。请改为使用 CAST 或 CONVERT。
GetReparentedValue :可以用来移动节点(或者子树)
ToString :将hierarchyid转换为字符串,,与parse正好相反
Write
Write 将 SqlHierarchyId 的二进制表示形式写出到传入的 BinaryWriter 中。无法通过使用 Transact-SQL 来调用 Write。请改为使用 CAST 或 CONVERT。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

Setting up a wireless network is common, but choosing or changing the network type can be confusing, especially if you don't know the consequences. If you're looking for advice on how to change the network type from public to private or vice versa in Windows 11, read on for some helpful information. What are the different network profiles in Windows 11? Windows 11 comes with a number of network profiles, which are essentially sets of settings that can be used to configure various network connections. This is useful if you have multiple connections at home or office so you don't have to set it all up every time you connect to a new network. Private and public network profiles are two common types in Windows 11, but generally

Part 1 Let’s talk about the nature of Python sequence types. In this blog, let’s talk about Python’s various “sequence” classes and the three built-in commonly used data structures – list, tuple and character. The nature of the string class (str). I don’t know if you have noticed it, but these classes have an obvious commonality. They can be used to save multiple data elements. The most important function is: each class supports subscript (index) access to the elements of the sequence, such as using SyntaxSeq[i]. In fact, each of the above classes is represented by a simple data structure such as an array. However, readers familiar with Python may know that these three data structures have some differences: for example, tuples and strings cannot be modified, while lists can.

With the popularity of short video platforms, video matrix account marketing has become an emerging marketing method. By creating and managing multiple accounts on different platforms, businesses and individuals can achieve goals such as brand promotion, fan growth, and product sales. This article will discuss how to effectively use video matrix accounts and introduce different types of video matrix accounts. 1. How to create a video matrix account? To make a good video matrix account, you need to follow the following steps: First, you must clarify what the goal of your video matrix account is, whether it is for brand communication, fan growth or product sales. Having clear goals helps develop strategies accordingly. 2. Choose a platform: Choose an appropriate short video platform based on your target audience. The current mainstream short video platforms include Douyin, Kuaishou, Huoshan Video, etc.

On the occasion of releasing the build 26040 version of Windows Server, Microsoft announced the official name of the product: Windows Server 2025. Also launched is the Windows11WindowsInsiderCanaryChannel version build26040. Some friends may still remember that many years ago someone successfully converted Windows NT from workstation mode to server mode, showing the commonalities between various versions of Microsoft operating systems. Although there are clear differences between Microsoft's current version of the server operating system and Windows 11, those who pay attention to the details may be curious: why Windows Server updated the brand,

How to modify the default name of nginx, you can disguise it a little, or you can install Tip: Generally, modifications are made before nginx is compiled. After modification, the code needs to be recompiled as follows: scr/core/nginx.conf#definenginx_version"1.4.7"#definenginx_ver"nginx/"n

Go functions can return multiple values of different types. The return value type is specified in the function signature and returned through the return statement. For example, a function can return an integer and a string: funcgetDetails()(int,string). In practice, a function that calculates the area of a circle can return the area and an optional error: funccircleArea(radiusfloat64)(float64,error). Note: If the function signature does not specify a type, a null value is returned; it is recommended to use a return statement with an explicit type declaration to improve readability.

It’s great to use dynamic language for a while, and the code is reconstructed in the crematorium. I believe you must have heard this sentence. Like unit testing, although it takes a small amount of time to write code, it is very worthwhile in the long run. This article shares how to better understand and use Python's type hints. 1. Type hints are only valid at the syntax level. Type hints (introduced since PEP3107) are used to add types to variables, parameters, function parameters, and their return values, class properties, and methods. Python's variable types are dynamic and can be modified at runtime to add type hints to the code. It is only supported at the syntax level and has no impact on the running of the code. The Python interpreter will ignore the type hints when running the code. Therefore the type
