WITH Args AS (SELECT * FROM Department
WHERE supervisor='mary' UNION ALL SELECT A.* FROM Department
A, Args WHERE A.ParentNode = Args.FullCode )
select distinct * from Args inner join Department b on Args.FullCode=b.BUDepart昨天问来如下一段代码,结果发现在sql server 2000里面with关键字不能用。
有什么替代的么?急死啦!
WHERE supervisor='mary' UNION ALL SELECT A.* FROM Department
A, Args WHERE A.ParentNode = Args.FullCode )Args inner join Department b on Args.FullCode=b.BUDepart
(SELECT * FROM Department
WHERE supervisor='mary' UNION ALL SELECT A.* FROM Department
A, Args WHERE A.ParentNode = Args.FullCode) a inner join Department b on a.FullCode=b.BUDepart
我有下面一张表,表里的记录是: FullDept Dept ParentDept Supervisor
----------------------------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi 现在我要做报表,假如mary登录系统,判断她是S-IT-CN的头,看到底下所有的子部门。 就是结果: FullDept Dept ParentDept Supervisor
----------------------------
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack 但是不要显示上一级的。 如果是CiCi登录系统,则结果该是所有的记录都有。
FullDept Dept ParentDept Supervisor
----------------------------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi
如果是Jerry,就是:
FullDept Dept ParentDept Supervisor
----------------------------
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
所有分全拿出来了。
from ( SELECT * FROM Department WHERE supervisor='mary'
UNION ALL
SELECT A.* FROM Department A, Args WHERE A.ParentNode = Args.FullCode) Args
inner join Department b on Args.FullCode=b.BUDepart
INSERT @TB
SELECT 'S-IT', 'IT', 'S', 'Peter' UNION ALL
SELECT 'S-IT-CN', 'CN', 'S-IT', 'Mary' UNION ALL
SELECT 'S-IT-CN-SH', 'SH', 'S-IT-CN', 'Jack' UNION ALL
SELECT 'S-FS-AP', 'AP', 'S-FS', 'Colin' UNION ALL
SELECT 'S-FS', 'FS', 'S', 'Jerry' UNION ALL
SELECT 'S', '0', '0', 'CiCi'DECLARE @T TABLE([FullDept] VARCHAR(10), [Dept] VARCHAR(2), [ParentDept] VARCHAR(7), [Supervisor] VARCHAR(5), LVL INT)
DECLARE @LVL INT
SET @LVL=0
INSERT @T
SELECT *,@LVL FROM @TB WHERE [Supervisor]='Jerry'
WHILE @@ROWCOUNT>0
BEGIN
SET @LVL=@LVL+1
INSERT @T
SELECT A.*,@LVL
FROM @TB A, @T B
WHERE A.[ParentDept]=B.[FullDept]
AND B.LVL=@LVL-1
ENDSELECT * FROM @T
/*
FullDept Dept ParentDept Supervisor LVL
---------- ---- ---------- ---------- -----------
S-FS FS S Jerry 0
S-FS-AP AP S-FS Colin 1
*/
go
create table [Department]([FullDept] varchar(10),[Dept] varchar(2),[ParentDept] varchar(7),[Supervisor] varchar(5))
insert [Department]
select 'S-IT','IT','S','Peter' union all
select 'S-IT-CN','CN','S-IT','Mary' union all
select 'S-IT-CN-SH','SH','S-IT-CN','Jack' union all
select 'S-FS-AP','AP','S-FS','Colin' union all
select 'S-FS','FS','S','Jerry' union all
select 'S','0','0','CiCi'
go
select * from [Department]--Mary login in..
select * from Department
where FullDept like (select FullDept from Department where Supervisor='Mary')+'%'
/*
FullDept Dept ParentDept Supervisor
---------- ---- ---------- ----------
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack(2 行受影响)
*/
--CiCi login in...
select * from Department
where FullDept like (select FullDept from Department where Supervisor='CiCi')+'%'
/*
FullDept Dept ParentDept Supervisor
---------- ---- ---------- ----------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi(6 行受影响)
*/
FullDept Dept ParentDept Supervisor
---------- ---- ---------- ----------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS mary
S-FS FS S Jerry
S 0 0 CiCi
go
create table [Department]([FullDept] varchar(10),[Dept] varchar(2),[ParentDept] varchar(7),[Supervisor] varchar(5))
insert [Department]
select 'S-IT','IT','S','Peter' union all
select 'S-IT-CN','CN','S-IT','Mary' union all
select 'S-IT-CN-SH','SH','S-IT-CN','Jack' union all
select 'S-FS-AP','AP','S-FS','mary' union all
select 'S-FS','FS','S','Jerry' union all
select 'S','0','0','CiCi'
go
select * from [Department]
--Mary login in..
select a.* from Department a
join Department b on a.FullDept like b.FullDept+'%'
and b.Supervisor='Mary'
/*
FullDept Dept ParentDept Supervisor
---------- ---- ---------- ----------
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS mary(3 行受影响)
*/
--CiCi login in...
select a.* from Department a
join Department b on a.FullDept like b.FullDept+'%'
and b.Supervisor='CiCi'
/*
FullDept Dept ParentDept Supervisor
---------- ---- ---------- ----------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS mary
S-FS FS S Jerry
S 0 0 CiCi(6 行受影响)
*/