3个表
A(id,name)主表
B(a_id,name)关联表
C(a_id,name)关联表A-〉B(一对多)
A-〉C(一对多)如何查询符合b.name并且符合c.name的A记录
如何查询符合b.name或者符合c.name的A记录
想寻一条简单的Sql语句,不吝赐教~,多谢
A(id,name)主表
B(a_id,name)关联表
C(a_id,name)关联表A-〉B(一对多)
A-〉C(一对多)如何查询符合b.name并且符合c.name的A记录
如何查询符合b.name或者符合c.name的A记录
想寻一条简单的Sql语句,不吝赐教~,多谢
from (a inner join b on a.name=b.name) inner join c on a.name=c.nameorselect a.id,a.name
from a,b,c
where a.name=b.name
and a.name=c.name[align=center]==== 思想重于技巧 ====
[/align]
from a
where a.name in (select name from b union select name from c)or select a.id,a.name
from a
where a.name inner join (select name from b union select name from c) t
on a.name=t.nameorselect a.id,a.name
from a inner join b on a.name=b.name
union
select a.id,a.name
from a inner join c on a.name=c.name[align=center]==== 思想重于技巧 ====
[/align]
符合B.name并且符合c.name,意思是b.name=“中国”并且c.name=“人民”
符合B.name或者符合c.name,意思是b.name=“中国”或者c.name=“人民”而且每个表的记录都超过了50万的集合,有什么办法能很快地检索出来呢
from (a inner join b on a.id=b.id) inner join c on a.id=c.id
or
select a.id,a.name
from a,b,c
where a.id=b.id
and a.id=c.id
[align=center]==== 思想重于技巧 ====
[/align]
效率较好
select a.id,a.name
from a nner join (select id from b union select id from c) t
on a.id=t.id效率其次
select a.id,a.name
from a inner join b on a.id=b.id
union
select a.id,a.name
from a inner join c on a.id=c.id效率较差
select a.id,a.name
from a
where a.id in (select id from b union select id from c)
[align=center]==== 思想重于技巧 ====
[/align]
from (a left join b on a.id=b.id) left join c on a.id=c.id
where b.id is not null or c.id is not null[align=center]==== 思想重于技巧 ====
[/align]
符合B.name或者符合c.name,意思是b.name=“中国”或者c.name=“人民”
但是能不能把下面的条件加上阿符合B.name并且符合c.name,意思是b.name=“中国”并且c.name=“人民”
符合B.name或者符合c.name,意思是b.name=“中国”或者c.name=“人民”
能阿,直接加就是了
符合B.name并且符合c.name,意思是b.name=“中国”并且c.name=“人民”
and b.name='中国' and c.name='人民'
符合B.name或者符合c.name,意思是b.name=“中国”或者c.name=“人民”
and b.name='中国' or c.name='人民'[align=center]==== 思想重于技巧 ====
[/align]