数据库 SQLServer 递归查询。SQLServer 递归查询基本概念基本语法
- WITH expression_name [ ( column_name [,...n] ) ]AS( CTE_query_definition )--只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的。--运行 CTE 的语句为:SELECT FROM expression_name;
实例
- use dtdl;
- WITH TEST_CTEAS(SELECT TBIE.FSTABLENAME, TBIE.FSID, TBIE.FTTABLENAME, TBIE.FTID, TBIE.FROUTEID FROM T_BF_INSTANCEENTRY TBIEWHERE TBIE.FTTABLENAME = '1'AND TBIE.FTID = '1'UNION ALLSELECT CTBIE.FSTABLENAME, CTBIE.FSID, CTBIE.FTTABLENAME, CTBIE.FTID, CTBIE.FROUTEID FROM T_BF_INSTANCEENTRY CTBIEINNER JOIN TEST_CTE CTE ON CTBIE.FSID = CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME) SELECT * FROM TEST_CTE--限制递归次数OPTION(MAXRECURSION 10)
扩展 - 构造递归路径
- WITH TEST_CTEAS(SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID,Cast(TBIE.FTID as nvarchar(4000)) AS PATHFROM T_BF_INSTANCEENTRY TBIEWHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625UNION ALLSELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID,CTE.PATH+'->'+Cast(CTBIE.FTID as nvarchar(4000)) PATH FROM T_BF_INSTANCEENTRY CTBIEINNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME)SELECT * FROM TEST_CTE --限制递归次数OPTION(MAXRECURSION 10)
Oracle 递归查询基本概念基本语法
- select colname from tablenamestart with 条件1connect by 条件2where 条件3;
实例
- SELECT * FROM T_BF_INSTANCEENTRYSTART WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')CONNECT BY FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME
扩展:构造递归路径
- SELECT TBIE. * ,
- SUBSTR(SYS_CONNECT_BY_PATH(FTID, '->'), 3) NAME_PATH FROM T_BF_INSTANCEENTRY TBIESTART WITH(FTID = 100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY') CONNECT BY FSID = PRIOR FTID AND FSTABLENAME = PRIOR FTTABLENAME
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: