当前位置:首页 » 编程语言 » sqlnotinexcept

sqlnotinexcept

发布时间: 2024-05-20 02:53:10

A. sql not in 两个表找不同的问题 100分在线等……

SQLSERVER版本:

select * from t1 t where t.a1 in(
select a1 from t1 except select a1 from t2 )

附上我的测试sql:
create table #1(a1 varchar(10),a2 nvarchar(10),a3 varchar(10));
create table #2(a1 varchar(10),a2 nvarchar(10),a3 varchar(10));
insert into #1( a1,a2,a3)values('01','张三','90'),('02','李四','85'),('03','王五','89');
insert into #2( a1,a2,a3)values('01','张三','98'),('04','谢六','88'),('05','陈四','87');
select * from #1 t where t.a1 in(
select a1 from #1 except select a1 from #2 )
drop table #1;
drop table #2;

ORACLE版本:

select * from t1 t where t.a1 in(
select a1 from t1 minus select a1 from t2 );

附上测试sql:

create table t1(a1 varchar(10),a2 nvarchar2(10),a3 varchar(10));
create table t2(a1 varchar(10),a2 nvarchar2(10),a3 varchar(10));
insert into t1( a1,a2,a3) select * from (
select '01' a1 ,'张三' a2,'90' a3 from al
union
select '02' a1 ,'李四' a2,'85' a3 from al
union
select '03' a1 ,'李四' a2,'89' a3 from al);
insert into t2( a1,a2,a3) select * from (
select '01' a1 ,'张三' a2,'98' a3 from al
union
select '04' a1 ,'谢六' a2,'88' a3 from al
union
select '05' a1 ,'陈四' a2,'87' a3 from al);
select * from t1 t where t.a1 in(
select a1 from t1 minus select a1 from t2 );
drop table t1;
drop table t2;

热点内容
linuxsnmp安装 发布:2025-07-02 23:07:08 浏览:650
北理c语言答案 发布:2025-07-02 23:05:57 浏览:304
sql同比 发布:2025-07-02 23:03:39 浏览:835
一个服务器获取多个ip 发布:2025-07-02 23:02:43 浏览:786
三星电脑wifi怎么连接wifi密码 发布:2025-07-02 22:55:00 浏览:712
安卓开发选哪个版本的SDK 发布:2025-07-02 22:19:07 浏览:486
未上传图片 发布:2025-07-02 22:14:01 浏览:599
安卓qq直播怎么分享声音 发布:2025-07-02 22:13:21 浏览:250
安卓系统怎么刷机清除内存 发布:2025-07-02 22:08:19 浏览:697
安卓手机6位数密码有多少组 发布:2025-07-02 21:50:31 浏览:798