需要ANSI 92递归SQL语句

I am translating SQL Server SQL Statements into their ANSI generic equivalent at present, and am stuck with a recursive statement using a WITH statement.

我目前正在将SQL Server SQL语句转换为它们的ANSI通用等价物,并且使用WITH语句使用递归语句。

For the sake of concentrating on the issue, I'll simplify the issue as follows

为了集中讨论这个问题,我将简化问题如下

If I have two tables

如果我有两张桌子

ReportingUnit
- col1: Key
- col2: ParentReportingUnitKey

ReportingUnit col1:Key col2:ParentReportingUnitKey

Facility
- col1: Key
- col2: ParentReportingUnitKey

Facility col1:Key col2:ParentReportingUnitKey

This structure is describing a hierarchy of reporting units down to a facility, where a reporting unit may have 0 .. 1 direct parent reporting units and 0 .. n child reporting units.

该结构描述了直到设施的报告单元的层次结构,其中报告单元可以具有0..1直接父报告单元和0 ... n子报告单元。

A facility is a 'leaf' record, that links to a reporting unit.

设施是“叶子”记录,链接到报告单位。

I need to craft an ANSI 92 valid SQL Statement (or at worst one that will work on Oracle, DB2 and SQL Server) that will return all facilities related to a given reporting unit anywhere up the hierarchy.

我需要制作一个ANSI 92有效的SQL语句(或者最坏的一个可以在Oracle,DB2和SQL Server上运行的语句),它将在层次结构的任何地方返回与给定报告单元相关的所有设施。

e.g.

ReportingUnit R1 has ReportingUnit children R1.1 and R1.2

ReportingUnit R1具有ReportingUnit子级R1.1和R1.2

ReportingUnit R1.1 has children R1.1.1, R1.1.2

ReportingUnit R1.1具有子R1.1.1,R1.1.2

ReportingUnit R1.2 has children R1.2.1, R1.2.2

ReportingUnit R1.2具有子级R1.2.1,R1.2.2
Facility F1 has a parent reporting unit R1.1.1

设施F1有一个父报告单位R1.1.1
Facility F2 has a parent reporting unit R1.1.2

设施F2具有父报告单元R1.1.2

Facility F3 has a parent reporting unit R1.2.1

设施F3具有父报告单元R1.2.1

Facility F4 has a parent reporting unit R1.2.2

设施F4有一个父报告单位R1.2.2

Bearing in mind there are may be 0 .. n levels of recursion in the ReportingUnit table, how can I return all 4 facilities from a SQL Statement given the parameter ReportingUnit=R1?

请记住,ReportingUnit表中可能有0 .. n级递归,如果给定参数ReportingUnit = R1,如何从SQL语句返回所有4个工具?

2 个解决方案

#1

I'm tolerably certain that no recursive statements were available in SQL-92; the earliest version where that was supported was SQL-99.

我可以肯定在SQL-92中没有可用的递归语句;最支持的版本是SQL-99。

Consequently, you are stuck with not using SQL-92. Why do you think SQL-92 is desirable? Is it as a base level of SQL functionality, or is there some other reason?

因此,您不会使用SQL-92。为什么你认为SQL-92是可取的?它是SQL功能的基础级别,还是有其他原因?

Current versions of DB2 have the WITH clause and can achieve recursive queries. I believe Oracle has the WITH clause too; I'm not sure whether it can achieve recursive queries using them. Oracle also has the wholly non-standard and non-relational CONNECT BY PRIOR. I'm not sure what MS SQL Server supports.

当前版本的DB2具有WITH子句,可以实现递归查询。我相信Oracle也有WITH子句;我不确定它是否可以使用它们实现递归查询。 Oracle还拥有完全非标准和非关系的CONNECT BY PRIOR。我不确定MS SQL Server支持什么。

There is a reasonably strong chance that you will be unable to find a single syntax that is supported by all three of your specified DBMS.

您很可能无法找到所有三个指定DBMS支持的单一语法。

#2

There is no SQL-92 solution for recursive queries.

递归查询没有SQL-92解决方案。

The best option is to use one of the solutions for encoding hierarchical relationships so that you can query all descendants or ancestors, using standard SQL.

最好的选择是使用其中一种解决方案来编码层次关系,以便您可以使用标准SQL查询所有后代或祖先。

See a brief description here: "What is the most efficient/elegant way to parse a flat table into a tree?".

请在此处查看简要说明:“将平台解析为树的最有效/最优雅的方法是什么?”。

Or read "Trees and Hierarchies in SQL for Smarties" by Joe Celko.

或者阅读Joe Celko撰写的“适用于Smarties的SQL中的树和层次结构”。

#1