挑战一下～看看这条SQL应该怎么写
有如下数据：
表 yuefen:
帐号 月份
m662280612 4
m662285226 4
m663100013 4
m663100025 4
m663100026 4
m663100089 4
m663100090 4
m663100096 4
m662280612 3
m662285226 3
m663100013 3
m663100025 3
m663100089 2
m663100090 2
m663100096 2
m662280612 2
m662285226 2
表 ziliao：
帐号 资料1 资料2
m662280612 资612 资612
m662285226 资5226 资5226
m663100013 资13 资13
m663100025 资025 资025
m663100026 资26 资26
m663100089 资331 资331
m663100090 资90 资90
m663100096 资96 资96
现在需要得到如下结果：
查找 yuefen 表中 月份 为4但月份不为2和3的账号，同时将账号与表 ziliao中的资料对应并显示。
这条SQL应该怎么写呢？
还有就是查找 yuefen 表中 月份 为2和3但月份不为4的账号,该如何写呢？
先谢谢各位了～
[解决办法]
select a.帐号,
b.资料1,
b.资料2,
from yuefen a
left join ziliao b on a.帐号=b.帐号
where a.月份 = 4 --a.月份 not in (2,3)
[解决办法]
1.
select a.帐号,
b.资料1,
b.资料2,
from yuefen a
left join ziliao b on a.帐号=b.帐号
where a.月份 = 4 and a.月份 not in (2,3)
2.
select a.帐号,
b.资料1,
b.资料2,
from yuefen a
left join ziliao b on a.帐号=b.帐号
where a.月份 <> 4 and a.月份 in (2,3)
[解决办法]
try:
select a.* from ziliao a left join (select * from yuefen where 月份=4) b
on a.帐号=b.帐号
select a.* from ziliao a left join (select * from yuefen where 月份=2 or 月份=3) b
on a.帐号=b.帐号
select a.* from ziliao a left join (select * from yuefen where 月份 in(2,3)) b
on a.帐号=b.帐号
[解决办法]
select a.帐号,a.月份,b.资料1，b.资料2
from yuefen a，ziliao b
where a.帐号＝b.帐号 and a.月份＝‘4’
select * from yuefen a where a.yuefen in( '2 ', '3 ')
[解决办法]
select a.* from ziliao a left join yuefen b
on a.帐号=b.帐号 where b.月份=4
select a.* from ziliao a left join yuefen b
on a.帐号=b.帐号 where b.月份=2 or b.月份=3
[解决办法]
create table yuefen(帐号 varchar(20),月份 int)
insert yuefen select 'm662280612 ',4
union all select 'm662285226 ',4
union all select 'm663100013 ',4
union all select 'm663100025 ',4
union all select 'm663100026 ',4
union all select 'm663100089 ',4
union all select 'm663100090 ',4
union all select 'm663100096 ',4
union all select 'm662280612 ',3
union all select 'm662285226 ',3
union all select 'm663100013 ',3
union all select 'm663100025 ',3
union all select 'm663100089 ',2
union all select 'm663100090 ',2
union all select 'm663100096 ',2
union all select 'm662280612 ',2
union all select 'm662285226 ',2
create table ziliao(帐号 varchar(20),资料1 varchar(20),资料2 varchar(20))
insert ziliao select 'm662280612 ', '资612 ', '资612 '
union all select 'm662285226 ', '资5226 ', '资5226 '
union all select 'm663100013 ', '资13 ', '资13 '
union all select 'm663100025 ', '资025 ', '资025 '
union all select 'm662285226 ', '资26 ', '资26 '
union all select 'm663100089 ', '资331 ', '资331 '
union all select 'm662285226 ', '资5226 ', '资5226 '
union all select 'm663100090 ', '资90 ', '资90 '
union all select 'm663100096 ', '资96 ', '资96 '
select yuefen.帐号,资料1,资料2 from yuefen inner join ziliao
on yuefen.帐号=ziliao.帐号 where 月份=4 and 月份 not in(2,3)
帐号 资料1 资料2
-------------------- -------------------- --------------------
m662280612 资612 资612
m662285226 资5226 资5226
m662285226 资26 资26
m662285226 资5226 资5226
m663100013 资13 资13
m663100025 资025 资025
m663100089 资331 资331
m663100090 资90 资90
m663100096 资96 资96
[解决办法]
--用Exists的方法
--1.
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where Not Exists (Select 帐号 From yuefen Where 帐号 = A.帐号 And (月份 = 2 Or 月份 = 3))
And A.月份 = 4
--2.
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where Not Exists (Select 帐号 From yuefen Where 帐号 = A.帐号 And 月份 = 4)
And A.月份 = 2 Or A.月份 = 3
