Create Procedure QueryEmp @DepName varchar(20) as ;with cte as ( select id,parent from A where DepName=@DepName union all select t2.id,t2.parent from cte t1 join A t2 on t1.id=t2.parent ) select t1.* from B t1 join cte t2 on t1.Dep_Id=t2.id
Create table TA(Id int,DepName nvarchar(30),Parent int) Create table TB(Id int,Name nvarchar(30),Age int,Dep_Id int)GO insert into TA Select 1 ,N'总经办', -1 insert into TA Select 2 ,N'生产部', 1 insert into TA Select 3 ,N'一车间', 2 insert into TA Select 4 ,N'二车间', 2 insert into TA Select 5 ,N'三车间', 2 insert into TA Select 6 ,N'四车间', 2 insert into TA Select 7 ,N'销售部', 1 insert into TA Select 8 ,N'内销', 7 insert into TA Select 9 ,N'外销', 7 insert into TA Select 10 ,N'财务部', 1GO insert into TB select 1 ,N'黑猪', 22, 3 insert into TB select 2 ,N'白猪', 22 , 3 insert into TB select 3 ,N'大猪', 22 , 4 insert into TB select 4 ,N'小猪', 21 , 4 insert into TB select 5 ,N'中猪', 22 , 5 insert into TB select 6 ,N'职业猪', 22 , 6 insert into TB select 7 ,N'菜鸟猪', 25 , 8 insert into TB select 8 ,N'白痴猪', 24 , 9 insert into TB select 9 ,N'职业玩家', 30 , 10 insert into TB select 10 ,N'职职业玩', 10 , 10GOCreate Procedure QueryEmp @DepName nvarchar(30) as ;with cte as ( select id,parent from TA where DepName=@DepName union all select t2.id,t2.parent from cte t1 join TA t2 on t1.id=t2.parent ) select t1.* from TB t1 join cte t2 on t1.Dep_Id=t2.idGO exec QueryEmp N'总经办'/* Id Name Age Dep_Id ----------- ------------------------------ ----------- ----------- 1 黑猪 22 3 2 白猪 22 3 3 大猪 22 4 4 小猪 21 4 5 中猪 22 5 6 职业猪 22 6 7 菜鸟猪 25 8 8 白痴猪 24 9 9 职业玩家 30 10 10 职职业玩 10 10(10 個資料列受到影響)*/
select top 15.* from View_NoComm where commentid not in (select top 0.Commentid from View_NoComm) 后面的top 0 的话就查询不到第一条数据 高手 高手高手高手高手高手高手高手高手高手
Create Procedure QueryEmp
@DepName varchar(20)
as
;with cte as
(
select id,parent from A where DepName=@DepName
union all
select t2.id,t2.parent from cte t1 join A t2 on t1.id=t2.parent
)
select t1.*
from B t1 join cte t2 on t1.Dep_Id=t2.id
Create table TA(Id int,DepName nvarchar(30),Parent int)
Create table TB(Id int,Name nvarchar(30),Age int,Dep_Id int)GO
insert into TA Select 1 ,N'总经办', -1
insert into TA Select 2 ,N'生产部', 1
insert into TA Select 3 ,N'一车间', 2
insert into TA Select 4 ,N'二车间', 2
insert into TA Select 5 ,N'三车间', 2
insert into TA Select 6 ,N'四车间', 2
insert into TA Select 7 ,N'销售部', 1
insert into TA Select 8 ,N'内销', 7
insert into TA Select 9 ,N'外销', 7
insert into TA Select 10 ,N'财务部', 1GO
insert into TB select 1 ,N'黑猪', 22, 3
insert into TB select 2 ,N'白猪', 22 , 3
insert into TB select 3 ,N'大猪', 22 , 4
insert into TB select 4 ,N'小猪', 21 , 4
insert into TB select 5 ,N'中猪', 22 , 5
insert into TB select 6 ,N'职业猪', 22 , 6
insert into TB select 7 ,N'菜鸟猪', 25 , 8
insert into TB select 8 ,N'白痴猪', 24 , 9
insert into TB select 9 ,N'职业玩家', 30 , 10
insert into TB select 10 ,N'职职业玩', 10 , 10GOCreate Procedure QueryEmp
@DepName nvarchar(30)
as
;with cte as
(
select id,parent from TA where DepName=@DepName
union all
select t2.id,t2.parent from cte t1 join TA t2 on t1.id=t2.parent
)
select t1.*
from TB t1 join cte t2 on t1.Dep_Id=t2.idGO
exec QueryEmp N'总经办'/*
Id Name Age Dep_Id
----------- ------------------------------ ----------- -----------
1 黑猪 22 3
2 白猪 22 3
3 大猪 22 4
4 小猪 21 4
5 中猪 22 5
6 职业猪 22 6
7 菜鸟猪 25 8
8 白痴猪 24 9
9 职业玩家 30 10
10 职职业玩 10 10(10 個資料列受到影響)*/