遇到多表连接查询就头晕,呵呵
有如下三个表:
infant:(幼儿园表)
infantId
1
2
3
-------------
active:(活动表)
activId activName activYear
1 aa 2004
2 bb 2004
3 aa 2005
4 aa 2006
-----------------
infant_active:(连接表)
id infantId activId
1 1 1
2 1 2
3 2 3
4 2 2
---------------------------
问题: 1.我想查询出所有参加过2004-2005年活动的幼儿园ID,即infantIdinfantId
1
2
----------
2.我想查询出所有参加过2004-2005年活动为aa的幼儿园IDinfantId
1
-----------
3.我不太明白 left join on 和join on 有很大区别吗?
当我要查询所有参与过活动的幼儿园时,这样写就查不出我要的结果:
select infant.infantId from infant left join infant_active inf_a on infant.infantId = inf_a.infantId
可是当我去掉left 时,就正确了: select infant.infantId from infant join infant_active inf_a on infant.infantId = inf_a.infantId
大家可不可以帮我分析一下呢,多谢:)
有如下三个表:
infant:(幼儿园表)
infantId
1
2
3
-------------
active:(活动表)
activId activName activYear
1 aa 2004
2 bb 2004
3 aa 2005
4 aa 2006
-----------------
infant_active:(连接表)
id infantId activId
1 1 1
2 1 2
3 2 3
4 2 2
---------------------------
问题: 1.我想查询出所有参加过2004-2005年活动的幼儿园ID,即infantIdinfantId
1
2
----------
2.我想查询出所有参加过2004-2005年活动为aa的幼儿园IDinfantId
1
-----------
3.我不太明白 left join on 和join on 有很大区别吗?
当我要查询所有参与过活动的幼儿园时,这样写就查不出我要的结果:
select infant.infantId from infant left join infant_active inf_a on infant.infantId = inf_a.infantId
可是当我去掉left 时,就正确了: select infant.infantId from infant join infant_active inf_a on infant.infantId = inf_a.infantId
大家可不可以帮我分析一下呢,多谢:)
没听明白?请继续看下文分解。2,分解:还是用一个例子来的痛快些declare @a table(a int,b int)
declare @b table(a int,b int)insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右:
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外:
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b
cross join 是笛卡儿乘积 就是一张表的行数乘以另一张表的行数
left join 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
full join 返回两张表中的行 left join+right join
inner join 只返回两张表连接列的匹配项
from infant a inner join infant_active b on a.infantid=b.infantid
inner join active c on b.activid=c.activid
where c.activYear between 2004 and 2005
select a.infantid
from infant a inner join infant_active b on a.infantid=b.infantid
inner join active c on b.activid=c.activid
where c.activYear between 2004 and 2005 and c.activName='aa'
select distinct a.infantId from infant_active a,active b where b.activYear in (2004,2005) and b.activName = 'aa' and a.activId = b.activId
用 LEFT JOIN 运算 创建左边外部联接.左边外部联接将包含了从第一个(左边)开始的两个表中的全部记录,即使在第二个(右边)表中并没有相符值的记录。
用RIGHT JOIN 运算 创建 右边外部联接.右边外部联接将包含了从第二个(右边)开始的两个表中的全部记录,即使在第一个(左边)表中并没有匹配值的记录。
create table active(activId int,activName varchar(10),activYear int)
insert into active values(1, 'aa', 2004 )
insert into active values(2, 'bb', 2004 )
insert into active values(3, 'aa', 2005 )
insert into active values(4, 'aa', 2006 )
create table infant_active(id int, infantId int, activId int)
insert into infant_active values(1, 1, 1 )
insert into infant_active values(2, 1, 2 )
insert into infant_active values(3, 2, 3 )
insert into infant_active values(4, 2, 2 )
goselect distinct a.infantId from infant_active a,active b where b.activYear in (2004,2005) and a.activId = b.activId
/*infantId
-----------
1
2(2 行受影响)
*/
select distinct a.infantId from infant_active a,active b where b.activYear in (2004,2005) and b.activName = 'aa' and a.activId = b.activId
/*infantId
-----------
1
2(2 行受影响)
*/drop table active , infant_active
(select infantId from infant_active where activId in(select activId from active where year(activYear) between 2005 and 2004) )
select infantId from infant where infantId in
(select infantId from infant_active where activId in(select activId from active where (year(activYear) between 2005 and 2004) and activName='aa') )
我有一个连接表infant_active啊,infant和active两表是多对多关系,所以active表加一个infantId的关联字段不合适吧?:)