f********e
发帖数: 166 | 1 一个表
CREATE TABLE Item
(
ItemID INT NOT NULL
,ItemName VARCHAR(10) NOT NULL
,ParentID INT NULL
)
要求写个procedure
ItemID | ItemName | ParentID | Level
1 Hei NULL 1
3 Mii 1 2
怎么得到这个level用sql? | x***y
发帖数: 633 | | q*******l
发帖数: 300 | 3 这个得首先要清楚一些边界条件, 比如 parent ID doesn't exist in the item Table
, what's the behavior?
以下是假设 non-null parent ID will be existed in the table. 当然你可以写一个
udf 使用递归算出level.
下面的思路是不用递归,你可以引入一表变量, 仅含item ID & Level (初始为0 if
parent ID is not null, otherwise set it as 1 ), 然后 join it with item table
, and set the item's level as level + 1 if its parent Id item's level is not
0, use while loop until you can't find any 0. Finally, join this table and
item table to get the results. | r****t
发帖数: 10904 | 4 不知道 recursion 是不是必要的，按前面说的 recursion 了一下，(free RDBMS里面
只有 postgres 支持，sqlite/mysql 不支持)
with recursive
relation as (ItemID, ItemName, ParentID, Level) as
(select ItemID, ItemName, ParentID, 1 as Level from Item
where ItemID = 1
union
select ichild.ItemID, ichild.ItemName, ichild.ParentID, relation.Level+1 as
Level
from Item iparent, Item ichild, relation
where iparent.ItemID = ichild.ParentID)
select * from relation; | B*****g
发帖数: 34098 | 5 recursive SQL is ANSI SQL standard。所以数据库不支持是不对的
Level+1 as
【在 r****t 的大作中提到】
: 不知道 recursion 是不是必要的，按前面说的 recursion 了一下，(free RDBMS里面
: 只有 postgres 支持，sqlite/mysql 不支持)
: with recursive
: relation as (ItemID, ItemName, ParentID, Level) as
: (select ItemID, ItemName, ParentID, 1 as Level from Item
: where ItemID = 1
: union
: select ichild.ItemID, ichild.ItemName, ichild.ParentID, relation.Level+1 as
: Level
: from Item iparent, Item ichild, relation
| f********e
发帖数: 166 | 6 大牛能贴个语句么？
Table
table
not
and
【在 q*******l 的大作中提到】
: 这个得首先要清楚一些边界条件, 比如 parent ID doesn't exist in the item Table
: , what's the behavior?
: 以下是假设 non-null parent ID will be existed in the table. 当然你可以写一个
: udf 使用递归算出level.
: 下面的思路是不用递归,你可以引入一表变量, 仅含item ID & Level (初始为0 if
: parent ID is not null, otherwise set it as 1 ), 然后 join it with item table
: , and set the item's level as level + 1 if its parent Id item's level is not
: 0, use while loop until you can't find any 0. Finally, join this table and
: item table to get the results.
| B*****g
发帖数: 34098 | 7 不是有人贴了吗？再不行到数据库版问，问之前请注明数据库类型和版本
一个
【在 f********e 的大作中提到】
: 大牛能贴个语句么？
:
: Table
: table
: not
: and
| f********e
发帖数: 166 | 8 好牛啊，学习啦，这个recursive SQL server支不支持啊？
Level+1 as
【在 r****t 的大作中提到】
: 不知道 recursion 是不是必要的，按前面说的 recursion 了一下，(free RDBMS里面
: 只有 postgres 支持，sqlite/mysql 不支持)
: with recursive
: relation as (ItemID, ItemName, ParentID, Level) as
: (select ItemID, ItemName, ParentID, 1 as Level from Item
: where ItemID = 1
: union
: select ichild.ItemID, ichild.ItemName, ichild.ParentID, relation.Level+1 as
: Level
: from Item iparent, Item ichild, relation
| q*******l
发帖数: 300 | 9 SQL Server 是支持recursive的, in SQL Server 2008, you can make it as
following:
with relation as
(select ItemID, ItemName, ParentID, 1 as Level from Item
where ParentID is null
union all
select i.ItemID, i.ItemName, i.ParentID, r.Level+1 as Level
from Item i
inner join relation r on r.ItemID = i.ParentID)
select * from relation | f********e
发帖数: 166 | 10 非常感谢！
【在 q*******l 的大作中提到】
: SQL Server 是支持recursive的, in SQL Server 2008, you can make it as
: following:
: with relation as
: (select ItemID, ItemName, ParentID, 1 as Level from Item
: where ParentID is null
: union all
: select i.ItemID, i.ItemName, i.ParentID, r.Level+1 as Level
: from Item i
: inner join relation r on r.ItemID = i.ParentID)
: select * from relation
| c******o
发帖数: 534 | |
|
