自动越级查询

[poeily]

表结构如下
CREATE TABLE [dbo].[pub_dept] (
[dept_id] [int] NOT NULL ,
[dept_name] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[upparent] [int] NOT NULL ,
[orderindex] [int] NOT NULL ,
[Industry_id] [int] NULL ,
[IsLink] [bit] NULL ,
[remark] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[Dept_Subject_id] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[status] [int] NULL ,
[creator] [int] NOT NULL ,
[create_date] [datetime] NOT NULL ,
[modifier] [int] NULL ,
[modify_date] [datetime] NULL
) ON [PRIMARY]
GO

select dept_id from pub_dept where (dept_id in (select dept_id from pub_dept where dept_id in (select dept_id from pub_dept where upparent=2) or dept_id in (select dept_id from pub_dept where upparent in( select dept_id from pub_dept where upparent=2)) or dept_id in (select dept_id from pub_dept where upparent in (select dept_id from pub_dept where upparent in( select dept_id from pub_dept where upparent=2)))))

我这sql语句查出第二级id为2的下面的五级内的所有id，但是我想做出的效果是如果有第五级，就查出第五级，如果没有第五级的话就查出第4级，类推

if EXISTS(select dept_id from pub_dept where upparent in (select dept_id from pub_dept where upparent in( select dept_id from pub_dept where upparent=15)))

SELECT dept_id FROM pub_dept WHERE dept_id IN (select dept_id from pub_dept where upparent in (select dept_id from pub_dept where upparent in( select dept_id from pub_dept where upparent=15)))

ELSE IF EXISTS(SELECT dept_id FROM pub_dept WHERE dept_id in (select dept_id from pub_dept where upparent in( select dept_id from pub_dept where upparent=15)))

SELECT dept_id FROM pub_dept WHERE dept_id in (select dept_id from pub_dept where upparent in( select dept_id from pub_dept where upparent=15))

ELSE IF EXISTS(SELECT dept_id FROM pub_dept WHERE dept_id in (select dept_id from pub_dept where dept_id in (select dept_id from pub_dept where upparent=15)))

SELECT dept_id FROM pub_dept WHERE dept_id in (select dept_id from pub_dept where dept_id in (select dept_id from pub_dept where upparent=15))

这sql语句可以查出我要的部分效果，不过呢，因为最上面那机构下面可能有几个下级机构，可是我这sql语句只能查出下级的一个机构的id,

sql太乱了，兄弟们将就的看一下，帮我想想怎么写阿

ELSE

SELECT dept_id FROM pub_dept WHERE dept_id in (select dept_id from pub_dept where upparent=15)

[poeily]

2楼你那个我有点看不懂 你那是固定了机构吗？可是我那机构是动态的，是一个公司的部门，他随时都可能加多个机构，

[poeily]

稍微顶上一点

[antiking]

递归的。你加再多，都是递归下去的。

[poeily]

还是看不懂你那个，我拷过来放数据库又报错，想看例子都看不了

[antiking]

参考这个。
http://topic.csdn.net/u/20100603/09/38aece21-cd57-4faf-9f49-cf3c611fa3ec.html

[yzf86211861]

顶下吧 太乱了眼睛花了