select top 1 * from tTask where Tas_ID in (select Wkf_TaskID from tWorkFlow where (Wkf_Status='Await response' )and Wkf_Name='cc');
select * from tTask where Tas_ID in ( select Wkf_TasID from tWorkFlow a where Wkf_Status='Await response' and exists(select 1 from tWorkFlow where Wkf_Status='Approve' and Wkf_TasID=a.Wkf_TasID and Wkf_ID=a.Wkf_ID-1 ) )
select * from tTask where Tas_ID in (select Wkf_TaskID from tWorkFlow a where a.Wkf_Status='Await response' and a.Wkf_Name='cc' and exists (select * from tWorkFlow b where a.Wkf_ID = b.Wkf_ID + 1 and b.Wkf_Status = 'Approve'));
select * from tTask where exists( select * from tWorkFlow t1 join tWorkFlow t2 on t1.wkf_id=t2.wkf_id+1 where t1.Wkf_Status='Await response' and t2.Wkf_Status='Approve' and t1.Wkf_TasID=ttask.tas_id)
--上面的查询要求Wkf_ID是连续的--如果Wkf_ID不连续,就用: select * from @tTask where Tas_ID in ( select Wkf_TasID from @tWorkFlow a where Wkf_Status='Await response' and exists( select 1 from @tWorkFlow where Wkf_Status='Approve' and Wkf_TasID=a.Wkf_TasID and Wkf_ID=( select min(Wkf_ID) from @tWorkFlow where Wkf_TasID=a.Wkf_TasID ) ) )
--上面的查询是测试时用的,修改一下,改为正式表--如果Wkf_ID不连续,就用: select * from tTask where Tas_ID in ( select Wkf_TasID from tWorkFlow a where Wkf_Status='Await response' and exists( select 1 from tWorkFlow where Wkf_Status='Approve' and Wkf_TasID=a.Wkf_TasID and Wkf_ID=( select min(Wkf_ID) from tWorkFlow where Wkf_TasID=a.Wkf_TasID ) ) )
--下面是测试--测试数据 declare @tTask table(Tas_ID int,Tas_name varchar(2),Tas_Description varchar(2)) insert into @tTask select 1,'aa','aa' union all select 2,'bb','bb'declare @tWorkFlow table(Wkf_ID int,Wkf_TasID int,Wkf_Status varchar(20),Wkf_Name varchar(2)) insert into @tWorkFlow select 1,1,'Approve','aa' union all select 2,1,'Approve','bb' union all select 3,1,'Await response','cc' union all select 4,2,'Await response','bb' union all select 5,2,'Await response','cc'--Wkf_ID是连续的查询 select * from @tTask where Tas_ID in ( select Wkf_TasID from @tWorkFlow a where Wkf_Status='Await response' and exists(select 1 from @tWorkFlow where Wkf_Status='Approve' and Wkf_TasID=a.Wkf_TasID and Wkf_ID=a.Wkf_ID-1 ) ) --Wkf_ID不连续的查询: select * from @tTask where Tas_ID in ( select Wkf_TasID from @tWorkFlow a where Wkf_Status='Await response' and exists( select 1 from @tWorkFlow where Wkf_Status='Approve' and Wkf_TasID=a.Wkf_TasID and Wkf_ID=( select min(Wkf_ID) from @tWorkFlow where Wkf_TasID=a.Wkf_TasID ) ) ) /*--测试结果Tas_ID Tas_name Tas_Description ----------- -------- --------------- 1 aa aa(所影响的行数为 1 行) Tas_ID Tas_name Tas_Description ----------- -------- --------------- 1 aa aa(所影响的行数为 1 行) --*/
主表tTask Tas_ID Tas_name Tas_Description 1 aa aa 2 bb bb 3 cc cc
子表tWorkFlow Wkf_ID Wkf_TasID Wkf_Status Wkf_Name 1 1 Approve aa 2 1 Approve bb 3 1 Await response cc
4 2 Await response bb 5 2 Await response cc 6 3 Await response cc 如上面的主表当用户cc登录进来的时候TasID=1的时候满足条件返回ID,而TasID=2的时候他的上一条记录的Status不是Approve,所以就不返回ID。TasID=3应该可以返回ID的
select * from tTask where Tas_ID in (
select Wkf_TasID from tWorkFlow a
where Wkf_Status='Await response'
and exists(select 1 from tWorkFlow
where Wkf_Status='Approve'
and Wkf_TasID=a.Wkf_TasID
and Wkf_ID=a.Wkf_ID-1
)
)
那位能帮我想想呢?谢谢!
select * from @tTask where Tas_ID in (
select Wkf_TasID from @tWorkFlow a
where Wkf_Status='Await response'
and exists(
select 1 from @tWorkFlow
where Wkf_Status='Approve'
and Wkf_TasID=a.Wkf_TasID
and Wkf_ID=(
select min(Wkf_ID) from @tWorkFlow
where Wkf_TasID=a.Wkf_TasID
)
)
)
select * from tTask where Tas_ID in (
select Wkf_TasID from tWorkFlow a
where Wkf_Status='Await response'
and exists(
select 1 from tWorkFlow
where Wkf_Status='Approve'
and Wkf_TasID=a.Wkf_TasID
and Wkf_ID=(
select min(Wkf_ID) from tWorkFlow
where Wkf_TasID=a.Wkf_TasID
)
)
)
declare @tTask table(Tas_ID int,Tas_name varchar(2),Tas_Description varchar(2))
insert into @tTask
select 1,'aa','aa'
union all select 2,'bb','bb'declare @tWorkFlow table(Wkf_ID int,Wkf_TasID int,Wkf_Status varchar(20),Wkf_Name varchar(2))
insert into @tWorkFlow
select 1,1,'Approve','aa'
union all select 2,1,'Approve','bb'
union all select 3,1,'Await response','cc'
union all select 4,2,'Await response','bb'
union all select 5,2,'Await response','cc'--Wkf_ID是连续的查询
select * from @tTask where Tas_ID in (
select Wkf_TasID from @tWorkFlow a
where Wkf_Status='Await response'
and exists(select 1 from @tWorkFlow
where Wkf_Status='Approve'
and Wkf_TasID=a.Wkf_TasID
and Wkf_ID=a.Wkf_ID-1
)
)
--Wkf_ID不连续的查询:
select * from @tTask where Tas_ID in (
select Wkf_TasID from @tWorkFlow a
where Wkf_Status='Await response'
and exists(
select 1 from @tWorkFlow
where Wkf_Status='Approve'
and Wkf_TasID=a.Wkf_TasID
and Wkf_ID=(
select min(Wkf_ID) from @tWorkFlow
where Wkf_TasID=a.Wkf_TasID
)
)
)
/*--测试结果Tas_ID Tas_name Tas_Description
----------- -------- ---------------
1 aa aa(所影响的行数为 1 行)
Tas_ID Tas_name Tas_Description
----------- -------- ---------------
1 aa aa(所影响的行数为 1 行)
--*/
Tas_ID Tas_name Tas_Description
1 aa aa
2 bb bb
3 cc cc
子表tWorkFlow
Wkf_ID Wkf_TasID Wkf_Status Wkf_Name
1 1 Approve aa
2 1 Approve bb
3 1 Await response cc
4 2 Await response bb
5 2 Await response cc
6 3 Await response cc
如上面的主表当用户cc登录进来的时候TasID=1的时候满足条件返回ID,而TasID=2的时候他的上一条记录的Status不是Approve,所以就不返回ID。TasID=3应该可以返回ID的