34,593
社区成员
发帖
与我相关
我的任务
分享
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
*/