select * from t_1 a where not exists ( select 1 from t_2 b where a.C1 = b.P1 and a.C2 = b.P2 ) 呵呵,冒牌的正确吧
你是說要找 t1 里邊有而 t2 里邊沒有的記錄嗎﹐試試這個看 Select * From t1 Where c1+c2 Not in (Select p1+p2 From t2)
create table #t1(c1 int,c2 int) create table #t2(p1 int,p2 int) insert into #t1 select 1,1 union all select 1,2 insert into #t2 select 1,1 select a.* from #t1 a,#t2 b where a.c1<>b.p1 or a.c2<>b.p2
insert into t_2(P1,P2) select distinct a.C1,a.C2 from t_1 a left join t_2 b on a.C1 = b.P1 where a.c1 is null
改成insert into t_2(P1,P2) select --distinct --distinct可以不用 a.C1,a.C2 from t_1 a left join t_2 b on a.C1 = b.P1 and a.c2=b.p2 --关键少了条件 where a.c1 is null
snowwolf613(苦行僧) 有点对了。 不过如果两列都是INT型的,两列和相同就还是不对了, 比方 t_1中是(1,2) t_2中是(2,1)。其他人的 好象都不对。wangdehao(找找找) 和 Supernpc(世上不变的唯有变化): 别人看懂了,你们看懂了吗???? rea1gz(冒牌realgz V0.3) :自己建个两张表跑跑。不必在这里讲得掷地有声的样子! where a.c1 is null ,一条都选不出来了!
呵呵,楼主教训的是看来xiaonvjing(飞扬)的回复还有一个错误 where a.c1 is null 应该改成 where b.p1 is null 不过本冒牌这次出来,打定了一个主意,决不测试 错了是自己学艺不精,有人指出就是自己学习的机会上面的答案应该不止一个是正确的,冒牌是这么认为的,其他人怎么认为也就管不着了 snowwolf613(苦行僧)的怎么说都是错的
select * from t_1 where cast(t_1.ida1 as varchar(8))+cast(t_1.ida2 as varchar(8)) not in (select cast(t_2.idb1 as varchar(8))+cast(t_2.idb2 as varchar(8)) from t_2)
select * from t_1 where cast(t_1.c1 as varchar(8))+cast(t_1.c2 as varchar(8)) not in (select cast(t_2.p1 as varchar(8))+cast(t_2.p2 as varchar(8)) from t_2)学习!
Select * From t1 a Where not exists(Select 1 from t2 b where convert(varchar,isnull(a.c1,''))+convert(varchar,isnull(a.c2,''))<> convert(varchar,isnull(b.p1,''))+convert(varchar,isnull(b.p2,''))
Select * From t_1 a Where not exists(Select 1 from t_2 b where convert(varchar,isnull(a.c1,''))+convert(varchar,isnull(a.c2,''))<> convert(varchar,isnull(b.p1,''))+convert(varchar,isnull(b.p2,''))
from t_1 a
where not exists (
select 1
from t_2 b
where a.C1 = b.P1
and a.C2 = b.P2
)
呵呵,冒牌的正确吧
Select *
From t1
Where c1+c2 Not in
(Select p1+p2 From t2)
create table #t2(p1 int,p2 int)
insert into #t1
select 1,1 union all select 1,2
insert into #t2
select 1,1
select a.* from #t1 a,#t2 b
where a.c1<>b.p1 or a.c2<>b.p2
回复人: xiaonvjing(飞扬) ( ) 信誉:100 2005-06-10 14:59:00 得分: 0
insert into t_2(P1,P2) select distinct a.C1,a.C2
from t_1 a left join t_2 b on a.C1 = b.P1 where a.c1 is null
改成insert into t_2(P1,P2) select
--distinct --distinct可以不用
a.C1,a.C2
from t_1 a left join t_2 b
on a.C1 = b.P1 and a.c2=b.p2 --关键少了条件
where a.c1 is null
不过如果两列都是INT型的,两列和相同就还是不对了,
比方 t_1中是(1,2) t_2中是(2,1)。其他人的 好象都不对。wangdehao(找找找) 和 Supernpc(世上不变的唯有变化): 别人看懂了,你们看懂了吗???? rea1gz(冒牌realgz V0.3) :自己建个两张表跑跑。不必在这里讲得掷地有声的样子! where a.c1 is null ,一条都选不出来了!
where a.c1 is null
应该改成
where b.p1 is null 不过本冒牌这次出来,打定了一个主意,决不测试
错了是自己学艺不精,有人指出就是自己学习的机会上面的答案应该不止一个是正确的,冒牌是这么认为的,其他人怎么认为也就管不着了
snowwolf613(苦行僧)的怎么说都是错的
where
cast(t_1.ida1 as varchar(8))+cast(t_1.ida2 as varchar(8))
not in (select cast(t_2.idb1 as varchar(8))+cast(t_2.idb2 as varchar(8)) from t_2)
where
cast(t_1.c1 as varchar(8))+cast(t_1.c2 as varchar(8))
not in
(select cast(t_2.p1 as varchar(8))+cast(t_2.p2 as varchar(8)) from t_2)学习!
From t1 a
Where not exists(Select 1 from t2 b
where convert(varchar,isnull(a.c1,''))+convert(varchar,isnull(a.c2,''))<>
convert(varchar,isnull(b.p1,''))+convert(varchar,isnull(b.p2,''))
From t_1 a
Where not exists(Select 1 from t_2 b
where convert(varchar,isnull(a.c1,''))+convert(varchar,isnull(a.c2,''))<>
convert(varchar,isnull(b.p1,''))+convert(varchar,isnull(b.p2,''))