a1表:
id typename
1 a
2 b
3 c
4 d
5 e
a2表:
id tppeid isopen
1 1 0
2 1 1
3 2 0
4 4 1
5 2 0
6 3 0
7 3 0求语句,想查询a1和a2结果:a1.id a1.typename a2.isopen
2 b 0
3 c 0
解释:连接两个表不重复typename.查询a2表中isopen为0.如果isopen为1的tppeid将不参与(如:a2表中isopen为1的tppeid有1,4).
id typename
1 a
2 b
3 c
4 d
5 e
a2表:
id tppeid isopen
1 1 0
2 1 1
3 2 0
4 4 1
5 2 0
6 3 0
7 3 0求语句,想查询a1和a2结果:a1.id a1.typename a2.isopen
2 b 0
3 c 0
解释:连接两个表不重复typename.查询a2表中isopen为0.如果isopen为1的tppeid将不参与(如:a2表中isopen为1的tppeid有1,4).
from a1,a2
where a1.id=a2.tppeid
and not exists (
select 1 from a2
where tppeid=a1.id and isopen<>0
)
from a1
where not exists (
select 1 from a2
where tppeid=a1.id and isopen<>0
)
and exists (
select 1 from a2
where tppeid=a1.id and isopen=0
)
from
a1 t
join
a2 t2 on t.ID=t2.tppeid and t2.isopen=0
where (select distinct(isopen) from tppeid=t2.tppeid)=1
from
a1 t
join
a2 t2 on t.ID=t2.tppeid and t2.isopen=0
where (select distinct(isopen) from a2 --
where tppeid=t2.tppeid)=1
select
t.ID,t.tppeid,t2.isopen
from
a1 t
join
a2 t2 on t.ID=t2.tppeid
where
not exists (select 1 from a2 where tppeid=t2.tppeid and isopen=1 )
select a1.id,a1.typeid,b.isopen from a1,(select distinct typeid from a2 c where not exists(select 1 from a2 where typeid=c.typeid and isopen!=0))b
where a.typeid=b.typeid
declare @a1 table(id int, typename nvarchar(10))
insert into @a1
select 1, 'a'
union all select 2,'b'
union all select 3,'c'
union all select 4, 'd'
union all select 5, 'e'
declare @a2 table(id int,tppeid int,isopen int)
insert into @a2
select 1, 1, 0
union all select 2, 1, 1
union all select 3, 2, 0
union all select 4, 4 , 1
union all select 5, 2, 0
union all select 6, 3, 0
union all select 7, 3 , 0 select distinct a.id,a.typename,b.isopen
from @a1 a,@a2 b
where a.id=b.tppeid
and exists (
select 1 from @a2 c
where c.tppeid=b.tppeid and c.id<>b.id and c.isopen=0 and b.isopen=0
)
/*(5 row(s) affected)(7 row(s) affected)
id typename isopen
----------- ---------- -----------
2 b 0
3 c 0(2 row(s) affected)
*/