Sorry for this maybe foolish question but i'm newbie to SQL Server. Here structure of my table of divisions at organization:
不好意思，这个问题有点傻，但是我是SQL Server新手。这里是我的组织部门划分表的结构:
id int(16) -- simply unique id
sub_id int(16) -- this id of parent division in this table (if zero, than it don't have parent)
name VARCHAR(200) -- name of division
I need to create a simply procedure which return me all id of subdivisions in some division (with top division id too). I think i need array with subid's in loop, but i don't know how to create array in SQL Serve o_0 (omg.. array exist in (T)SQL language ? =). Help please. Or maybe another way to get it?
我需要创建一个简单的过程，它返回某个除法中的所有子除法的id(也有*除法id)。我想我需要一个带有subid的数组，但是我不知道如何在SQL中创建数组o_0 (omg..)数组存在于(T)SQL语言中吗?=)。请帮助。或者换一种方法?
2 个解决方案
#1
3
If I have understood your question correctly you need a recursive CTE.
如果我正确地理解了你的问题，你需要一个递归的CTE。
CREATE PROCEDURE dbo.foo
@id int
AS
WITH divisions AS
(
SELECT id, sub_id, name
FROM YourTable
WHERE id = @id
UNION ALL
SELECT y.id, y.sub_id, y.name
FROM YourTable y
JOIN divisions d ON d.id = y.sub_id
)
SELECT id, sub_id, name
FROM divisions
#2
1
SELECT id
FROM table
WHERE sub_id = @target
UNION ALL
SELECT @target
#1
3
If I have understood your question correctly you need a recursive CTE.
如果我正确地理解了你的问题，你需要一个递归的CTE。
CREATE PROCEDURE dbo.foo
@id int
AS
WITH divisions AS
(
SELECT id, sub_id, name
FROM YourTable
WHERE id = @id
UNION ALL
SELECT y.id, y.sub_id, y.name
FROM YourTable y
JOIN divisions d ON d.id = y.sub_id
)
SELECT id, sub_id, name
FROM divisions
#2
1
SELECT id
FROM table
WHERE sub_id = @target
UNION ALL
SELECT @target