try: ------------------------------------------------------- select a.*, to_char(Created,'yyyy-mm-dd hh:mi:ss') Createdx from TWORKITEM a where exists( (select personkey from (select personkey from TWORKITEMPERSON where WORKITEMKEY=a.WORKITEMKEY and WORKSTAT=1 and personkey<>-1) b where rownum = 1 )=24) and state = (select PKEY from TSTATE where LABEL='正在处理')
select a.*,to_char(Created,'yyyy-mm-dd hh:mi:ss') Createdx from TWORKITEM a where exists ( select 'x' from TWORKITEMPERSON where WORKITEMKEY=a.WORKITEMKEY and WORKSTAT=1 and personkey<>-1 and person key=24 ) and state=(select PKEY from TSTATE where LABEL='正在处理') ;
不用Exist,把那个 a表一起查就可以了, sqlserver中不需要的,可oracle 需要呀 select a.*,to_char(Created,'yyyy-mm-dd hh:mi:ss') Createdx from TWORKITEM a where ( select personkey from (select b.personkey from TWORKITEMPERSON b,tworkitem a where b.WORKITEMKEY=a.WORKITEMKEY and WORKSTAT=1 and personkey<>-1)b where rownum=1 )=24 and state=(select PKEY from TSTATE where LABEL='正在处理')
-------------------------------------------------------
select
a.*,
to_char(Created,'yyyy-mm-dd hh:mi:ss') Createdx
from
TWORKITEM a
where
exists(
(select personkey
from
(select
personkey
from
TWORKITEMPERSON
where
WORKITEMKEY=a.WORKITEMKEY
and
WORKSTAT=1
and
personkey<>-1) b
where
rownum = 1 )=24)
and
state = (select
PKEY
from
TSTATE
where
LABEL='正在处理')
from TWORKITEM a
where exists
(
select 'x' from TWORKITEMPERSON where WORKITEMKEY=a.WORKITEMKEY
and WORKSTAT=1 and personkey<>-1 and person key=24
)
and state=(select PKEY from TSTATE where LABEL='正在处理')
;
sqlserver中不需要的,可oracle 需要呀
select a.*,to_char(Created,'yyyy-mm-dd hh:mi:ss') Createdx
from TWORKITEM a
where
(
select personkey from (select b.personkey from TWORKITEMPERSON b,tworkitem a where b.WORKITEMKEY=a.WORKITEMKEY
and WORKSTAT=1 and personkey<>-1)b where rownum=1
)=24
and state=(select PKEY from TSTATE where LABEL='正在处理')