一个sql问题,请大家帮忙

[rishengw]

有两张表，a，b
a的结构如下
ck int *
lb int *
bh int *
gg int *
mm char
nn int
b的结构如下
ck int *
lb int *
bh int *
gg int *
dd int
a,b表中带*号的为主键
想得到 a表中存在而b表中不存在的记录，对于多个主键不知道各位有没有好的办法？

现送100分，不够再给。。。。
着急着呢。。。。。

[chuifengde]

select *
from a left join b
on a.ck=b.ck and a.lb=b.lb and a.bh=b.bh and a.gg=b.gg
where b.ck is null

[昵称被占用了]

a表中存在而b表中不存在的记录:

select * from a
where not exists (
select 1 from b
where ck=a.ck and
    lb=a.lb and
    bh=a.bh and
    gg=a.gg
)

[dawugui]

select a.* from a where cast(ck as varchar) + ',' + cast(lb as varchar) + ',' + cast(bh as varchar) + ',' cast(gg as varchar) not in (select cast(ck as varchar) + ',' + cast(lb as varchar) + ',' + cast(bh as varchar) + ',' cast(gg as varchar) from b)

[leo_lesley]

鸟的方法好,不过楼主这也太浪费分了吧!

[fwacky]

create table rishengwA
(
   ck   int     ,
    lb   int     ,
    bh   int    ,
    gg   int    ,
    mm   char ,
    nn   int 
primary key(ck,lb,bh,gg)
)
create table rishengwB
(
    ck   int   ,
    lb   int    ,
    bh   int   , 
    gg   int   ,
    dd   int 
primary key(ck,lb,bh,gg)
)

delete rishengwA
insert into rishengwA
select 1,2,3,4,'A',8 union all 
select 1,2,3,5,'A',9 union all 
select 1,2,3,6,'A',10 union all 
select 1,2,3,7,'A',11 union all 
select 1,2,3,8,'A',12 union all 
select 1,2,3,9,'A',13 

delete rishengwB
insert into rishengwB
select 1,2,3,4,8 union all 
select 1,2,3,5,9 union all 
select 1,2,3,6,10 

select rishengwA.*
 from rishengwA   where  cast(ck as varchar(10)) +cast(lb as varchar(10))+cast(bh as varchar(10))+cast(gg as varchar(10))  not in 
  (select cast(ck as varchar(10)) +cast(lb as varchar(10))+cast(bh as varchar(10))+cast(gg as varchar(10)) as id from rishengwB)

===========================
1	2	3	7	A	11
1	2	3	8	A	12
1	2	3	9	A	13

[dawugui]

--相同
select a.* , b.* from a,b where a.ck = b.ck and a.lb = b.lb and a.bh = b.bh and a.gg = b.gg

--不同
--效率高
select a.* from where not exists(select 1 from b where ck = a.ck and lb = a.lb and bh = a.bh and gg = a.gg)
--效率不高
select a.* from a where cast(ck as varchar) + ',' + cast(lb as varchar) + ',' + cast(bh as varchar) + ',' cast(gg as varchar) not in (select cast(ck as varchar) + ',' + cast(lb as varchar) + ',' + cast(bh as varchar) + ',' cast(gg as varchar) from b)

[rishengw]

鸟同志的方法不对

[rishengw]

海阔天空 跟老乌龟的方法ok
谢谢大家

[昵称被占用了]

鸟的方法是不错的，效率不低，只是select的结果需要改下

select a.*
from a left join b
on a.ck=b.ck and a.lb=b.lb and a.bh=b.bh and a.gg=b.gg
where b.ck is null

[liyanmingkong]

想问一下，鸟同志的方法为什么不对啊？？

[昵称被占用了]

结贴有点太快了