<tfoot id='YbVGk'></tfoot>

      1. <legend id='YbVGk'><style id='YbVGk'><dir id='YbVGk'><q id='YbVGk'></q></dir></style></legend>

        <small id='YbVGk'></small><noframes id='YbVGk'>

        • <bdo id='YbVGk'></bdo><ul id='YbVGk'></ul>
        <i id='YbVGk'><tr id='YbVGk'><dt id='YbVGk'><q id='YbVGk'><span id='YbVGk'><b id='YbVGk'><form id='YbVGk'><ins id='YbVGk'></ins><ul id='YbVGk'></ul><sub id='YbVGk'></sub></form><legend id='YbVGk'></legend><bdo id='YbVGk'><pre id='YbVGk'><center id='YbVGk'></center></pre></bdo></b><th id='YbVGk'></th></span></q></dt></tr></i><div id='YbVGk'><tfoot id='YbVGk'></tfoot><dl id='YbVGk'><fieldset id='YbVGk'></fieldset></dl></div>

        SQL Server中Oracle的CONNECT BY PRIOR的模拟

        时间:2023-07-17
      2. <legend id='MPYnJ'><style id='MPYnJ'><dir id='MPYnJ'><q id='MPYnJ'></q></dir></style></legend>

        1. <small id='MPYnJ'></small><noframes id='MPYnJ'>

            <i id='MPYnJ'><tr id='MPYnJ'><dt id='MPYnJ'><q id='MPYnJ'><span id='MPYnJ'><b id='MPYnJ'><form id='MPYnJ'><ins id='MPYnJ'></ins><ul id='MPYnJ'></ul><sub id='MPYnJ'></sub></form><legend id='MPYnJ'></legend><bdo id='MPYnJ'><pre id='MPYnJ'><center id='MPYnJ'></center></pre></bdo></b><th id='MPYnJ'></th></span></q></dt></tr></i><div id='MPYnJ'><tfoot id='MPYnJ'></tfoot><dl id='MPYnJ'><fieldset id='MPYnJ'></fieldset></dl></div>
            • <tfoot id='MPYnJ'></tfoot>
                <bdo id='MPYnJ'></bdo><ul id='MPYnJ'></ul>
                    <tbody id='MPYnJ'></tbody>
                1. 本文介绍了SQL Server中Oracle的CONNECT BY PRIOR的模拟的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  如何在 SQL Server 2000/2005/2008 中获得 CONNECT BY PRIOR of Oracle 的功能?

                  解决方案

                  实现递归查询的 SQL 标准方法,例如由 IBM DB2 和 SQL Server 提供,是 WITH代码> 子句.请参阅这篇文章,了解翻译 的一个示例CONNECT BYWITH(技术上是递归 CTE)——这个例子是针对 DB2 的,但我相信它也适用于 SQL Server.

                  显然最初的 querant 需要一个特定的例子,这是来自 IBM 站点的一个,我已经给出了它的 URL.给定一个表:

                  CREATE TABLE emp(empid INTEGER NOT NULL PRIMARY KEY,名称 VARCHAR(10),工资 DECIMAL(9, 2),网格整数);

                  其中mgrid 引用员工经理的empid,任务是获取直接或间接向Joan 报告的每个人的姓名.在 Oracle 中,这是一个简单的 CONNECT:

                  选择名称发件人以姓名 = '琼' 开头CONNECT BY PRIOR empid = mgrid

                  在 SQL Server、IBM DB2 或 PostgreSQL 8.4 中(以及在 SQL 标准中,就其价值而言;-),完全等效的解决方案是递归查询(更复杂的语法,但实际上,甚至更多力量和灵活性):

                  WITH n(empid, name) AS(选择 empid,名称发件人WHERE name = '琼'联合所有选择 nplus1.empid,nplus1.nameFROM emp as nplus1, n其中 n.empid = nplus1.mgrid)从 n 中选择名称

                  Oracle 的 START WITH 子句成为第一个嵌套的 SELECT,递归的基本情况,与递归部分进行 UNION只是另一个SELECT.

                  SQL Server 的 WITH 的特定风格当然记录在 MSDN,其中还提供了使用此关键字的指导方针和限制,以及几个示例.

                  How can I get the functionality of CONNECT BY PRIOR of Oracle in SQL Server 2000/2005/2008?

                  解决方案

                  The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and SQL Server, is the WITH clause. See this article for one example of translating a CONNECT BY into a WITH (technically a recursive CTE) -- the example is for DB2 but I believe it will work on SQL Server as well.

                  Edit: apparently the original querant requires a specific example, here's one from the IBM site whose URL I already gave. Given a table:

                  CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                                   name   VARCHAR(10),
                                   salary DECIMAL(9, 2),
                                   mgrid  INTEGER);
                  

                  where mgrid references an employee's manager's empid, the task is, get the names of everybody who reports directly or indirectly to Joan. In Oracle, that's a simple CONNECT:

                  SELECT name 
                    FROM emp
                    START WITH name = 'Joan'
                    CONNECT BY PRIOR empid = mgrid
                  

                  In SQL Server, IBM DB2, or PostgreSQL 8.4 (as well as in the SQL standard, for what that's worth;-), the perfectly equivalent solution is instead a recursive query (more complex syntax, but, actually, even more power and flexibility):

                  WITH n(empid, name) AS 
                     (SELECT empid, name 
                      FROM emp
                      WHERE name = 'Joan'
                          UNION ALL
                      SELECT nplus1.empid, nplus1.name 
                      FROM emp as nplus1, n
                      WHERE n.empid = nplus1.mgrid)
                  SELECT name FROM n
                  

                  Oracle's START WITH clause becomes the first nested SELECT, the base case of the recursion, to be UNIONed with the recursive part which is just another SELECT.

                  SQL Server's specific flavor of WITH is of course documented on MSDN, which also gives guidelines and limitations for using this keyword, as well as several examples.

                  这篇关于SQL Server中Oracle的CONNECT BY PRIOR的模拟的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                      <bdo id='OLzto'></bdo><ul id='OLzto'></ul>
                        <tbody id='OLzto'></tbody>

                      1. <legend id='OLzto'><style id='OLzto'><dir id='OLzto'><q id='OLzto'></q></dir></style></legend>
                      2. <i id='OLzto'><tr id='OLzto'><dt id='OLzto'><q id='OLzto'><span id='OLzto'><b id='OLzto'><form id='OLzto'><ins id='OLzto'></ins><ul id='OLzto'></ul><sub id='OLzto'></sub></form><legend id='OLzto'></legend><bdo id='OLzto'><pre id='OLzto'><center id='OLzto'></center></pre></bdo></b><th id='OLzto'></th></span></q></dt></tr></i><div id='OLzto'><tfoot id='OLzto'></tfoot><dl id='OLzto'><fieldset id='OLzto'></fieldset></dl></div>
                      3. <small id='OLzto'></small><noframes id='OLzto'>

                      4. <tfoot id='OLzto'></tfoot>