有如下数据:
表 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的账号,该如何写呢?先谢谢各位了~
表 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的账号,该如何写呢?先谢谢各位了~
b.资料1,
b.资料2,
from yuefen a
left join ziliao b on a.帐号=b.帐号
where a.月份 = 4 --a.月份 not in (2,3)
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)
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where A.帐号 Not In (Select 帐号 From yuefen Where 月份 = 2 Or 月份 = 3)
And A.月份 = 4
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where A.帐号 Not In (Select 帐号 From yuefen Where 月份 = 4)
And A.月份 = 2 Or A.月份 = 3
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.帐号
from yuefen a,ziliao b
where a.帐号=b.帐号 and a.月份=‘4’select * from yuefen a where a.yuefen in('2','3')
on a.帐号=b.帐号 where b.月份=4select 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',2create 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
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
b.资料1,
b.资料2
from yuefen a
left join ziliao b on a.帐号=b.帐号
where a.月份 = 4 and a.月份 not in (2,3)==select a.帐号,
b.资料1,
b.资料2
from yuefen a
left join ziliao b on a.帐号=b.帐号
where a.月份 = 4因為“月份 = 4”的那條數據,他那條數據的月份就不可能為2,3了
--1.
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where A.帐号 Not In (Select 帐号 From yuefen Where 月份 = 2 Or 月份 = 3)
And A.月份 = 4
--2.
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where A.帐号 Not In (Select 帐号 From yuefen Where 月份 = 4)
And (A.月份 = 2 Or A.月份 = 3)
--方法二: (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)
(帐号 Char(10),
月份 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
(帐号 Char(10),
资料1 Nvarchar(20),
资料2 Nvarchar(20))
Insert ziliao Select 'm662280612', N'资612', N'资612'
Union All Select 'm662285226', N'资5226', N'资5226'
Union All Select 'm663100013', N'资13', N'资13'
Union All Select 'm663100025', N'资025', N'资025'
Union All Select 'm663100026', N'资26', N'资26'
Union All Select 'm663100089', N'资331', N'资331'
Union All Select 'm663100090', N'资90', N'资90'
Union All Select 'm663100096', N'资96', N'资96'
GO
--1.
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where A.帐号 Not In (Select 帐号 From yuefen Where 月份 = 2 Or 月份 = 3)
And A.月份 = 4
--2.
Select
A.*, B.资料1, B.资料2
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Where A.帐号 Not In (Select 帐号 From yuefen Where 月份 = 4)
And (A.月份 = 2 Or A.月份 = 3)
GO
Drop Table yuefen, ziliao
--Result
/*
帐号 月份 资料1 资料2
m663100026 4 资26 资26帐号 月份 资料1 资料2
*/
Select
A.*, B.资料1, B.资料2, C.帐号
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Left Join
yuefen C
On A.帐号 = C.帐号 And (C.月份 = 2 Or C.月份 = 3)
Where A.月份 = 4 And C.帐号 Is Null
--2.
Select
A.*, B.资料1, B.资料2, C.帐号
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Left Join
yuefen C
On A.帐号 = C.帐号 And C.月份 = 4
Where (A.月份 = 2 Or A.月份 = 3) And C.帐号 Is Null
谢谢您的解答!
还有一个,我想查在2 3月份都出现,但4月份未出现的数据,怎么写呢?我用这个:
Select A.*, B.zl1, B.zl2 From yuefen A Inner Join ziliao B On A.zh = B.zh Where A.zh Not In (Select zh From yuefen Where yf = 4) And (A.yf = 2 AND A.yf = 3)
不行阿~
A.*, B.资料1, B.资料2, C.帐号
From
yuefen A
Inner Join
ziliao B
On A.帐号 = B.帐号
Inner Join
yuefen C
On A.帐号 = C.帐号
Left Join
yuefen D
On A.帐号 = D.帐号 And D.月份 = 4
Where A.月份 = 2 And C.月份 = 3 And D.帐号 Is Null
ziliao B On A.帐号 = B.帐号
Where A.月份 = 4
这是查找 yuefen 表中 月份 为4但月份不为2和3的账号,同时将账号与表 ziliao中的资料对应并显示.
Select A.*, B.资料1, B.资料2 From yuefen A Inner Join
ziliao B On A.帐号 = B.帐号
Where A.月份 = 3 and A.月份 = 2
这是查找 yuefen 表中 月份 为2和3但月份不为4的账号,同时将账号与表 ziliao中的资料对应并显示.
Select A.*, B.资料1, B.资料2 From yuefen A Inner Join
ziliao B On A.帐号 = B.帐号
Where A.月份 = 4
这是查找 yuefen 表中 月份 为4但月份不为2和3的账号,同时将账号与表 ziliao中的资料对应并显示.------------------------
你這是“这是查找 yuefen 表中 月份 为4的账号,同时将账号与表 ziliao中的资料对应并显示.”,和樓主的需求有區別的.
where a.帐号=b.帐号
and a.月份 not in (2,3)select * from yuefen a ,ziliao b
where a.帐号=b.帐号
and a.月份 not in (4)
From yuefen a
left join ziliao b on a.帐号=b.帐号
Where a.月份 ='4'
select b.帐号,b.资料1,b.资料2
from ziliao b
where b.帐号 exist
(select a.帐号
from yuefen a
where a.月份 = 4)2、查找 yuefen表中月份为2、3的账号,同时将账号与表 ziliao中的资料对应sql;
select b.帐号,b.资料1,b.资料2
from ziliao b
where b.帐号 exist
(select a.帐号
from yuefen a
where a.月份 in(2,3) )3、2 3月份都出现,但4月份未出现的数据;
select b.帐号,b.资料1,b.资料2
from ziliao b
where b.帐号 exist
(select * from
(select b.帐号
from yuefen b
where b.月份in(2,3) ) bb
where bb.帐号 not in
(select distinct a.帐号
from yuefen a
where a.月份=4 ) )
第二个问题: 查找 yuefen 表中 月份 为2和3但月份不为4的账号高手 paoluo(一天到晚游泳的鱼) 都理解成:
yuefen 表中 月份 为2***或***3 但月份不为4的账号了所以应该还的再改改是吧.
刚看到.
sorry!
月份都 =4 了 当然不为 2 3 了 。。