高效查询亲子关系

发布于 2024-08-11 13:49:38 字数 782 浏览 6 评论 0原文

假设您有以下数据库表：

 create table Names (
  Id INT IDENTITY NOT NULL,
    Name NVARCHAR(100) not null,
    ParentNameId INT null,
    primary key (Id)
 )

 create index IX_Name on Names (Name)

 alter table Names
  add constraint FK_NameNames
  foreign key (ParentNameId) 
  references Names

这允许定义分层名称。每个名称可以有一个父名称和任意数量的子名称。

我希望找到与限定名称（例如“a：b：c”）相对应的记录，其中冒号分隔每个名称。我目前已经使用连接来完成此操作：

 select
  Id
 from
  Names names0
  inner join Names names1 on names0.ParentNameId = names1.Id
  inner join Names names2 on names1.ParentNameId = names2.Id
 where
  names0.Name = 'a' and
  names1.Name = 'b' and
  names2.Name = 'c' and
  names0.ParentNameId is null

我想知道是否有一种更有效的方法来执行此操作，而不涉及数据的非规范化或对任何特定 DBMS 的硬依赖。

谢谢

原文

Assuming you have the following database table:

 create table Names (
  Id INT IDENTITY NOT NULL,
    Name NVARCHAR(100) not null,
    ParentNameId INT null,
    primary key (Id)
 )

 create index IX_Name on Names (Name)

 alter table Names
  add constraint FK_NameNames
  foreign key (ParentNameId) 
  references Names

This allows the definition of hierarchical names. Each name can have one parent name, and any number of child names.

I wish to find the record corresponding to a qualified name such as "a:b:c", where colons delimit each name. I have currently done so using joins:

 select
  Id
 from
  Names names0
  inner join Names names1 on names0.ParentNameId = names1.Id
  inner join Names names2 on names1.ParentNameId = names2.Id
 where
  names0.Name = 'a' and
  names1.Name = 'b' and
  names2.Name = 'c' and
  names0.ParentNameId is null

What I'm wondering is whether there's a more efficient way to do this that does not involve denormalization of the data or taking a hard dependency on any particular DBMS.

Thanks

分享到QQ

分享到微博