sql-server - 如何将包含START WITH…CONNECT BY子查询的 View 转换为SQL Server？

Question

我正在尝试将 View 从Oracle RDBMS转换为SQL Server。该 View 如下所示:

create or replace view user_part_v
as
  select part_region.part_id, users.id as users_id
    from part_region, users
   where part_region.region_id in(select     region_id
                                        from region_relation
                                  start with region_id = users.region_id
                                  connect by parent_region_id = prior region_id)

阅读了有关 recursive CTE's及其 use in sub-queries的信息后，将上述内容转换为SQL Server语法的最佳猜测是:

create view user_part_v
as
  with region_structure(region_id, parent_region_id) as (
    select region_id
         , parent_region_id
      from region_relation
     where parent_region_id = users.region_id
    union all
    select r.region_id
         , r.parent_region_id
      from region_relation r
      join region_structure rs on rs.parent_region_id = r.region_id
  )
  select part_region.part_id, users.id as users_id
    from part_region, users
   where part_region.region_id in(select region_id from region_structure)

显然，这给了我关于CTE定义中对users.region_id的引用的错误。

如何在SQL Server中获得与从Oracle View 中获得的结果相同的结果？

背景

我正在研究从Oracle 11g RDMS上运行的系统到SQL Server 2008的转换。该系统是一个相对较大的基于Java EE的系统，使用JPA(休眠)从数据库中查询。

许多查询使用上述 View 将返回的结果限制为适合当前用户的结果。如果我不能直接转换 View ，那么转换将变得更加困难，因为我将需要更改查询数据库的所有位置以获得相同的结果。

该 View 引用的表的结构类似于:

USERS
  ID 
  REGION_ID

REGION
  ID
  NAME

REGION_RELATIONSHIP
  PARENT_REGION_ID
  REGION_ID

PART
  ID
  PARTNO
  DESCRIPTION

PART_REGION
  PART_ID
  REGION_ID

因此，我们将区域划分为层次结构。可以将用户分配给区域。零件可以分配给许多区域。用户只能看到分配给他们所在区域的零件。这些区域引用了各个地理区域:

World
  Europe
    Germany
    France
    ...
  North America
    Canada
    USA
      New York
      ...

如果将零件＃123分配给美国区域，并将用户分配给纽约区域，则用户应该可以看到该零件。

更新:我能够通过创建包含所需数据的单独 View 来解决该错误，然后将主 View 加入该 View 。这可以使系统正常工作，但是我尚未进行全面的正确性或性能测试。我仍然愿意寻求更好的解决方案的建议。

Answer 1

我重新格式化了您的原始查询，以使其更易于阅读。

create or replace view user_part_v
as
select part_region.part_id, users.id as users_id
from part_region, users
where part_region.region_id in(
    select region_id
    from region_relation
    start with region_id = users.region_id
    connect by parent_region_id = prior region_id
);

让我们检查一下该查询中发生了什么。

select part_region.part_id, users.id as users_id
from part_region, users

这是一个老式的联接，其中的表是笛卡尔联接的，然后通过后续的where子句减少结果。

where part_region.region_id in(
    select region_id
    from region_relation
    start with region_id = users.region_id
    connect by parent_region_id = prior region_id
);

使用connect by语句的子查询正在使用外部查询中的users表中的 region_id定义递归的起点。
然后， in子句检查以查看是否在递归查询的结果中找到了 region_id的 part_region。
此递归遵循 region_relation表中给出的父子链接。

因此，将in子句与引用父项和旧式联接的子查询结合在一起意味着，您必须考虑查询要完成的内容并从该方向进行处理(而不仅仅是经过重新调整的旧查询的排列方式)，以便能够将其转换为单个递归CTE。

如果将零件分配给沿区域层次结构同一分支的多个区域，则此查询还将返回多行。例如如果将零件同时分配给 北美和美国 美国，则分配给 纽约的用户将为其 users_id返回两行，并返回相同的 part_id号。

考虑到Oracle View 和您给出的 View 背景，我认为您正在寻找的东西更像是这样:

create view user_part_v
as
with user_regions(users_id, region_id, parent_region_id) as (
    select u.users_id, u.region_id, rr.parent_region_id
    from users u 
    left join region_relation rr on u.region_id = rr.region_id
    union all
    select ur.users_id, rr.region_id, rr.parent_region_id
    from user_regions ur
    inner join region_relation rr on ur.parent_region_id = rr.region_id
)
select pr.part_id, ur.users_id
from part_region pr
inner join user_regions ur on pr.region_id = ur.region_id;

请注意，我已经将 users_id添加到了递归CTE的输出中，然后只是对 part_region表和CTE结果进行了简单的内部联接。

让我为您分解查询。

select u.users_id, u.region_id, rr.parent_region_id
from users u 
left join region_relation rr on u.region_id = rr.region_id

这是我们递归的开始。我们将 region_relation表与 users表结合起来，以获得每个用户的递归起点。该起点是为用户分配的区域以及该区域的 parent_region_id。此处完成了 left join，并从 user表中拉出了region_id，以防用户被分配到最顶层的区域(这意味着 region_relation表中不会存在该区域的条目)。

select ur.users_id, rr.region_id, rr.parent_region_id
from user_regions ur
inner join region_relation rr on ur.parent_region_id = rr.region_id

这是CTE的递归部分。我们获取每个用户的现有结果，然后为每个用户在现有集合的父区域中添加行。这种递归会一直发生，直到我们用完父母。 (即，我们在 region_id表中命中了没有 region_relationship条目的行。)

select pr.part_id, ur.users_id
from part_region pr
inner join user_regions ur on pr.region_id = ur.region_id;

这是我们获取最终结果集的部分。假设(就像我从您的描述中所做的那样)，每个区域只有一个父对象(这意味着 region_relationship中只有一行，每个 region_id)，一个简单的联接将返回所有能够根据以下内容查看该零件的用户:零件的 region_id。这是因为对于用户的分配区域，每个用户恰好返回一行，而对于每个父区域，直到分层结构根，每个用户都有一行返回。

注意:

原始查询和此查询都具有一定的限制，我想确保您了解这一限制。如果该部分被分配给层次结构中比用户低的区域(即，该区域是用户区域的后代，例如该部分被分配给 纽约，而用户被分配给 美国而不是其他远)，用户将看不到该部分。必须将零件分配给用户分配的区域，或区域层次结构中较高的区域。

另一件事是，该查询仍然展示出我上面提到的有关原始查询的情况，即如果将一部分分配给沿继承关系的同一分支的多个区域，则对于 users_id和 part_id的相同组合，将返回多行。我这样做是因为我不确定您是否希望更改行为。

如果这实际上是一个问题，并且您想消除重复项，则可以用以下代码替换CTE下的查询:

select p.part_id, u.users_id
from part p
cross join users u
where exists (
    select 1
    from part_region pr
    inner join user_regions ur on pr.region_id = ur.region_id;
    where pr.part_id = p.part_id
    and ur.users_id = u.users_id
);

这样做会在 part表和 users表之间进行笛卡尔联接，然后仅返回子查询结果中两者的组合至少具有一行的行，这是我们要去重复的结果。