数据库-CSDN博客

本文链接：https://blog.csdn.net/susubuhui/article/details/4353638

1、增加一个字段

if not exists(select 1 from syscolumns where id=object_id('t_item') and name='oldnumber')
alter table t_item add oldnumber varchar(80) null

2、

SQL递归查询

今天用到一条查询语句,对于给定一个父结点, 取出存于表中的菜单的操作是比较容易的, 具体操作是这样的,:

表结构:

SQL> desc t_right
Name            Type          Nullable Default Comments
--------------- ------------- -------- ------- --------------------------------
RIGHT_ID        NUMBER(10)                     权限ID
RIGHT_NAME      VARCHAR2(24) Y         权限名称
RIGHT_TYPE      CHAR(1)                        权限类型 1:配置系统的权限 2 业务系统的权限 3报表系统
REMARK          VARCHAR2(120) Y           备注
CONTROL_TYPE    CHAR(1)       Y           控制类型 1菜单 2按键
PARENT_RIGHT_ID NUMBER(10)    Y       父结点ID

递归查找父结点为303下的所有子结点:

select * from t_right r
start with r.parent_right_id = 303 connect by prior r.right_id = r.parent_right_id ;

结果:

SQL> select * from t_right r
2 start with r.parent_right_id = 303 connect by prior r.right_id = r.parent_right_id ;

   RIGHT_ID RIGHT_NAME       RIGHT_TYPE REMARK     CONTROL_TYPE PARENT_RIGHT_ID
----------- -----------------------   ----------------    -----------    ------------               -----------------------------
      30304 工时池统计分析       2                                       1                           303
      30305 施工单查询              2                                       1                           303
      30301 超时单                    2                                       1                          303
   3030101 施工单                    2                                      1                           30301
   3030102 任务单                    2                                      1                          30301
      30302 异常单                   2                                       1                         303
   3030201 任务单                   2                                      1                           30302
      30303 工时池查询              2                                     1                           303
      30306 任务单查询              2                                      1                           303

9 rows selected

－－－－－－－

在oracle中通过connect by prior来实现递归查询

2007-12-30 10:14

收集的几条在oracle中通过connect by prior来实现递归查询

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);

插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id

===============================================================================================================

有一张表   t
字段：
parent
child
两个字段的关系是父子关系

写一个sql语句，查询出指定父下面的所有的子

比如

a   b
a   c
a   e
b   b1
b   b2
c   c1
e   e1
e   e3
d   d1

指定parent=a，选出
a   b
a   c
a   e
b   b1
b   b2
c   c1
e   e1
e   e3

SQL语句：
select   parent,child   from   test   start   with   parent='a'
connect   by   prior   child=parent

================================================================================================

connect by 是结构化查询中用到的，其基本语法是：
select ... from tablename start by cond1
connect by cond2
where cond3;
简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:
id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
COND2是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID，即本记录的父亲是上一条记录。
COND3是过滤条件，用于对返回的所有记录进行过滤。

PRIOR和START WITH关键字是可选项
PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR
运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是
自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为
可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询
条件的行作为根节点。
完整的例子如SELECT PID,ID,NAME FROM T_WF_ENG_WFKIND START WITH PID =0 CONNECT BY PRIOR ID = PID

以上主要是针对上层对下层的顺向递归查询而使用start with ... connect by prior ...这种方式,但有时在需求需要的时候,可能会需要由下层向上层的逆向递归查询,此是语句就有所变化:例如要实现 select * from table where id in ('0','01','0101','0203','0304') ;现在想把0304的上一级03给递归出来,0203的上一级02给递归出来,而01现在已经是存在的,最高层为0.而这张table不仅仅这些数据,但我现在只需要('0','01','0101','0203','0304','02','03')这些数据,此时语句可以这样写SELECT PID,ID,NAME FROM V_WF_WFKIND_TREE WHERE ID IN (SELECT DISTINCT(ID) ID FROM V_WF_WFKIND_TREE CONNECT BY PRIOR PID = ID START WITH ID IN ('0','01','0101','0203','0304') );

其中START WITH ID IN里面的值也可以替换SELECT 子查询语句.

注意由上层向下层递归与下层向上层递归的区别在于START WITH...CONNECT BY PRIOR...的先后顺序以及 ID = PID 和 PID = ID 的微小变化!

http://hi.baidu.com/ilkikyo/blog/item/c801a2f3624f1053352accaa.html

-----------查找某一节点所有子节点2009-07-17

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSubtreeInfo]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetSubtreeInfo]

go
CREATE FUNCTION dbo.GetSubtreeInfo

( @QuYuId AS varchar(50)

)

RETURNS @treeinfo table

( [QuYuId] [varchar] (50) NOT NULL,

[QuYuMiaoShu] [varchar] (100) NOT NULL,

[ShangJiId] [varchar] (50) NULL ,

[Level] [int] not null

) AS

BEGIN

DECLARE @level AS int

SELECT @level = 0

INSERT INTO @treeinfo

SELECT [QuYuId], [QuYuMiaoShu], [ShangJiId], @level

FROM [ipcam_zd_QuYu]

WHERE [QuYuId] = @QuYuId

WHILE @@ROWCOUNT > 0

BEGIN

SET @level = @level + 1

INSERT INTO @treeinfo

SELECT E.[QuYuId], E.[QuYuMiaoShu], E.[ShangJiId], @level

FROM [ipcam_zd_QuYu] AS E JOIN @treeinfo AS T

ON E.[ShangJiId] = T.[QuYuId] AND T.[Level] = @level - 1

END

RETURN

END

SELECT * FROM dbo.GetSubtreeInfo('QY001')