表F1
sn text
1 xin
2 xin1
5 xin2
表F2
psn sno desc
1 2 old
2 3 old1
3 4 old2
5 6 old3
6 7 old4
8 8 old5
9 9 old6
其中表F1.sn与F2.psn关联
F2的psn与 F2的 sno关联
要求: 查找出表F2中的记录,它的psn即不在表F1的sn中,同时也不与F2的sno关联:
具体就是
8 8 old5
9 9 old6
这两个记录。
谢谢
sn text
1 xin
2 xin1
5 xin2
表F2
psn sno desc
1 2 old
2 3 old1
3 4 old2
5 6 old3
6 7 old4
8 8 old5
9 9 old6
其中表F1.sn与F2.psn关联
F2的psn与 F2的 sno关联
要求: 查找出表F2中的记录,它的psn即不在表F1的sn中,同时也不与F2的sno关联:
具体就是
8 8 old5
9 9 old6
这两个记录。
谢谢
where psn not in(select sn from F1)
and psn not in(select sno from F1, F2 where F1.sn = F2.psn)
B.*
From
B
Left Join
A
On A.sn = B.psn
Where A.sn Is Null And B.psn = B.sno
where not exists(select 1 from F1 where sn = t.psn)
and not exists(select 1 from F1, F2 where F1.sn = F2.psn and F2.psn = t.sno)
echiynn,我和你思路一致 ,但就是不知道能不能得出正确结果
Create Table F1
(sn Int,
[text] Varchar(10))
Insert F1 Select 1, 'xin'
Union All Select 2, 'xin1'
Union All Select 5, 'xin2'
Create Table F2
(psn Int,
sno Int,
[desc] Varchar(10))
Insert F2 Select 1, 2, 'old'
Union All Select 2, 3, 'old1'
Union All Select 3, 4, 'old2'
Union All Select 5, 6, 'old3'
Union All Select 6, 7, 'old4'
Union All Select 8, 8, 'old5'
Union All Select 9, 9, 'old6'
GO
--測試
Select
B.*
From
F2 B
Left Join
F1 A
On A.sn = B.psn
Where A.sn Is Null And B.psn = B.sno
GO
--刪除測試環境
Drop Table F1, F2
--結果
/*
psn sno desc
8 8 old5
9 9 old6
*/
由于我没有测试环境,希望答案经过测试得出正确结果的
echiynn,我和你思路一致 ,但就是不知道能不能得出正确结果
------------------
你的結果和你的表述是不一致的。如果echiynn(寶玥)的思路是對的。我還是建議使用關聯的寫法,效率更優。Select B,*
From B Left Join A
On A.sn = B.psn
Left Join BC
On B.psn = C.sno
Where A.sn Is Null And C.sno Is Null
B.*
From
F2 B
Left Join
F1 A
On A.sn = B.psn
Where A.sn Is Null And B.psn = B.sno下面一種的寫法是Select B.*
From F2 B Left Join F1 A
On A.sn = B.psn
Left Join F2 C
On B.psn = C.sno
Where A.sn Is Null And C.sno Is Null不過下面這個寫法得出來的和你貼出來的結果不一樣,沒有任何數據。
sn int,
[text] varchar(10))
insert @a select 1, 'xin'
union all select 2, 'xin1'
union all select 5, 'xin2'declare @b table(
psn int,
sno int,
[desc] varchar(10))
insert @b select 1, 2, 'old'
union all select 2, 3, 'old1'
union all select 3, 4, 'old2'
union all select 5, 6, 'old3'
union all select 6, 7, 'old4'
union all select 8, 8, 'old5'
union all select 9, 9, 'old6'select * from @b
where psn not in(select sn from @a)
and psn not in(select sno from @a a, @b b where a.sn = b.psn)
/*
psn sno desc
----------- ----------- ----------
8 8 old5
9 9 old6(所影响的行数为 2 行)
*/
看來思路沒錯,呵呵
没有删除F2的
echiynn,我没有安装好,今天第一次上班,交给我 一个任务 ,还清多帮帮忙
From F2 B Left Join F1 A
On A.sn = B.psn
Left Join (Select F2.sno From F1 Inner Join F2 On F1.sn = F2.psn) C
On B.psn = C.sno
Where A.sn Is Null And C.sno Is Null
Create Table F1
(sn Int,
[text] Varchar(10))
Insert F1 Select 1, 'xin'
Union All Select 2, 'xin1'
Union All Select 5, 'xin2'
Create Table F2
(psn Int,
sno Int,
[desc] Varchar(10))
Insert F2 Select 1, 2, 'old'
Union All Select 2, 3, 'old1'
Union All Select 3, 4, 'old2'
Union All Select 5, 6, 'old3'
Union All Select 6, 7, 'old4'
Union All Select 8, 8, 'old5'
Union All Select 9, 9, 'old6'
GO
--測試
Select B.*
From F2 B Left Join F1 A
On A.sn = B.psn
Left Join (Select F2.sno From F1 Inner Join F2 On F1.sn = F2.psn) C
On B.psn = C.sno
Where A.sn Is Null And C.sno Is Null
GO
--刪除測試環境
Drop Table F1, F2
--結果
/*
psn sno desc
8 8 old5
9 9 old6
*/
例如:
/*
create table f1
(sn int,
des nvarchar(50))create table f2
(psn int,
sno int,
text1 nvarchar(50))insert into f1 select 1,'parent1'
union select 10,'parent2'insert into f2 select 1,2,'child1'
union select 2,3,'child2'
union select 3,4,'child3'
union select 8,8,'child8'
union select 9,9,'child9'
union select 10,11,'child10'
union select 11,12,'child11'
union select 12,13,'child12'
union select 13,14,'child13'
*/
select b.* from f2 b left join f1 a on a.sn =b.psn
left join
(select f2.sno from f1 inner join f2
on f2.psn=f1.sn) c
on b.psn=c.sno
where a.sn is null and c.sno is null
得出的结果就不对
应该得到
8 8 child8
9 9 child9
create table f1
(sn int,
des nvarchar(50))create table f2
(psn int,
sno int,
text1 nvarchar(50))insert into f1 select 1,'parent1'
union select 10,'parent2'insert into f2 select 1,2,'child1'
union select 2,3,'child2'
union select 3,4,'child3'
union select 8,18,'child18'
union select 9,19,''
union select 10,11,'child10'
union select 11,12,'child11'
union select 12,13,'child12'
union select 13,14,'child13'
得出 的结果
8 18 child18
9 19 child19
我的 方法是:
select * from f2 where psn not in
(select a.psn from f2 a , f2 b where a.psn=b.sno
union
select psn from f2 join f1 on f1.sn=f2.psn)
(sn int,
des nvarchar(50))create table f2
(psn int,
sno int,
text1 nvarchar(50))insert into f1 select 1,'parent1'
union select 10,'parent2'insert into f2 select 1,2,'child1'
union select 2,3,'child2'
union select 3,4,'child3'
union select 8,18,'child18'
union select 9,19,''
union select 10,11,'child10'
union select 11,12,'child11'
union select 12,13,'child12'
union select 13,14,'child13'
得出 的结果
8 18 child18
9 19 child19
------
select * from f2 where psn not in
(select a.psn from f2 a , f2 b where a.psn=b.sno
union
select psn from f2 join f1 on f1.sn=f2.psn)
------
我的是有结果的
我的方法能得出结果,但我的思路逻辑上游没有错误?
呵呵,开始我没有想到
学习了学习了~~lz你的思路逻辑上游没有错误?指什么意思啊
不过我倒还是喜欢这样用*/
select * from f2 where psn not in
(select a.psn from f2 a inner join f2 b on a.psn=b.sno呀 ---用inner join
union
select psn from f2 join f1 on f1.sn=f2.psn)
鱼老大呢?
我说鱼老大发表以下看法,因为数据很重要,一旦删除错了就不好了
(select a.psn from f2 a inner join f2 b on a.psn=b.sno
union
select a.psn from f2 a inner join f1 b on b.sn=a.psn) B
On A.psn = B.psn
Where B.psn Is Null
看你的語句,思路應該沒問題。
--***** 楼主给分把
---------------------------------------
drop table f1,f2
create table f1
(sn int,
des nvarchar(50))create table f2
(psn int,
sno int,
text1 nvarchar(50))insert into f1 select 1,'parent1'
union select 10,'parent2'
insert into f2 select 1,2,'child1'
union select 2,3,'child2'
union select 3,4,'child3'
union select 8,8,'child8'
union select 9,9,'child9'
union select 10,11,'child10'
union select 11,12,'child11'
union select 12,13,'child12'
union select 13,14,'child13'
select s.* from (select * from f2 s where not exists (select 1 from f2 where sno=s.psn and s.psn<>psn )) s where s.psn not in(select sn from f1)-----------结果
8 8 child8
9 9 child9
比如:
union select 120,120,'child10'
union select 121,121,'child11'
这也符合条件,但被你去掉了