有两个表
tb_test1
a1 a2
1 a
1 b
1 ctb_test2
b1 b2
1 a
1 b
1 d
2 c
条件:
a1=b1且a2 not in b2
得到的结果应该是
tb_test1中的 1 c
tb_test1
a1 a2
1 a
1 b
1 ctb_test2
b1 b2
1 a
1 b
1 d
2 c
条件:
a1=b1且a2 not in b2
得到的结果应该是
tb_test1中的 1 c
解决方案 »
- asp根据客户浏览的IP自动调用sql数据库里面的对应表数据
- 请高手帮忙,LINUX下使用SQL RELAY连接MSSQL 2000,连接故障
- 上面催的急,无奈再次发帖,修改存储过程
- PR*C中调用带返回游标的存储过程
- wince连接SQL2005的问题
- 两个关于T-sql语言的问题
- 问sql server 2000的数据文件,日志文件被盗,在另外的sql server 2000 服务器下是否可被读取,导致泄密?
- 请问大哥,那里有关存储过程的资料?
- SQL中对两个字段分组的查询问题
- 图片
- 如何用下面的方法来设置主键啊?
- ******sql的UPDATE问题,小小问题,只是我不会,在线等********
select a.*
from tb_test1 a left outer join tb_test2 b
on a.a1=b.b1 and a.a2<.b.b2
insert into @tb_test1 select 1,'a'
insert into @tb_test1 select 1,'b'
insert into @tb_test1 select 1,'c'declare @tb_test2 table(b1 int,b2 varchar(4))
insert into @tb_test2 select 1,'a'
insert into @tb_test2 select 1,'b'
insert into @tb_test2 select 1,'d'
insert into @tb_test2 select 2,'c'select
a.*
from
@tb_test1 a
where
exists(select 1 from @tb_test2 where b1=a.a1)
and
not exists(select 1 from @tb_test2 where b1=a.a1 and b2=a.a2)/*
a1 a2
----------- ----
1 c
*/
select a.* from tb_test1 a left join tb_test2 b on a.a1=b.b1 and a.a2=b.b2
where b.b1 is null
insert into tb_test1 select 1 , 'a'
union select 1 , 'b'
union select 1 , 'c'create table tb_test2(b1 int , b2 nvarchar(10))
insert into tb_test2
select 1 , 'a'
union select 1 , 'b'
union select 1 , 'd'
union select 2 , 'c'
select distinct a.*
from tb_test1 a left outer join tb_test2 b
on a.a1=b.b1
where a2 not in (select b2 from tb_test2 where a.a1=tb_test2.b1)--result
1 c
another way:
select a.* from tb_test1 a left join tb_test2 b on a.a1=b.b1 and a.a2=b.b2
where b.b1 is null
好方法~
INSERT INTO tb_test1
SELECT 1 ,'a' UNION ALL
SELECT 1 ,'b' UNION ALL
SELECT 1 ,'c'
CREATE TABLE tb_test2(b1 int,b2 nvarchar(2))
INSERT INTO tb_test2
SELECT 1 ,'a' UNION ALL
SELECT 1 ,'b' UNION ALL
SELECT 1 ,'d' UNION ALL
SELECT 2 ,'c'SELECT * FROM tb_test1 AS A WHERE A.a2 NOT IN(SELECT b2 FROM tb_test2 AS B WHERE A.a1=B.b1)DROP TABLE tb_test1,tb_test2
where exists(select 1 from tb_test2 where a.a1=b1 )
and not exists(select 1 from tb_test2 where a.a2=b2 )
on a.a1=b.b1
where a.a2 not in
(
select b2 from tb_test2 where b1=b.b1
)
select * from tb_test1 a
where not exists(select 1 from tb_test2 where a.a1=b1 and a.a2=b2 )
select a.* from tb_test1 a left join tb_test2 b on a.a1=b.b1 and a.a2=b.b2
where b.b1 is null只支持这2种好方法~HOHO