你给出的测试数据 13, 14 行sessionId , 和你图片中的不一样, 我按照图片的数据来的.
with tTask (id, sessionId, zd1, zd2) as
(
select 1, 1, 1, 0 union all
select 2, 1, 1, 1 union all
select 3, 2, 0, 0 union all
select 4, 3, 1, 0 union all
select 5, 3, 1, 0 union all
select 6, 3, 1, 0 union all
select 7, 4, 2, 0 union all
select 8, 4, 1, 1 union all
select 9, 5, 1, 1 union all
select 10, 5, 1, 1 union all
select 11, 6, 1, 0 union all
select 13, 6, 0, 0 union all
select 14, 6, 0, 0
)
, tSession (sessionId, userId) as
(
select 1, 1 union all
select 2, 2 union all
select 3, 3 union all
select 4, 4 union all
select 5, 5 union all
select 6, 4 union all
select 7, 5
)
, tUser (userId, userName)as
(
select 1, 'A' union all
select 2, 'B' union all
select 3, 'C' union all
select 4, 'D' union all
select 5, 'E'
)
, data as
(
select sessionid, min(zd1) as minzd1, min(zd2) as minzd2
from tTask
group by sessionid
)
select tuser.* from data
inner join tuser on tuser.userid = data.sessionid
where minzd1 = 1 and minzd2 = 0
and data.sessionid not in (select sessionid from tTask where zd1 = 2)