34,838
社区成员




select T1.功能, T1.状态, T1.ID, T2.状态, T2.ID
from t as T1 inner join t as T2 on (T1.ID = 101 and T2.ID = 102 and T1.功能 = T2.功能)
--drop table t
--go
create table t(自增主键 int, 功能 int,状态 int, ID int)
insert into t
select 1, 1, 1, 101
union all select 12, 2, 1, 101
union all select 3, 3, 1, 101
union all select 4, 4, 1, 101
union all select 5, 5, 1, 101
union all select 6, 1, 0, 102
union all select 7, 2, 0, 102
union all select 8, 3, 1, 102
union all select 9, 4, 1, 102
union all select 10, 5, 0, 102
union all select 11, 1, 0, 104
union all select 12, 2, 0, 104
union all select 13, 3, 1, 104
union all select 14, 4, 1, 104
union all select 15, 5, 1, 104
select t.功能,t1.状态,t1.ID,t2.状态,t2.ID
from
(
select distinct 功能
from t
where ID in (101,102)
)t
left join t t1
on t1.功能 = t.功能
and t1.id = 101
left join t t2
on t2.功能 = t.功能
and t2.id = 102
/*
功能 状态 ID 状态 ID
1 1 101 0 102
2 1 101 0 102
3 1 101 1 102
4 1 101 1 102
5 1 101 0 102
*/