當前位置:首頁 » 編程語言 » 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;

熱點內容
伺服器系統盤一般做什麼陣列 發布:2025-07-17 11:16:05 瀏覽:362
如何用c語音實現放射密碼 發布:2025-07-17 11:13:03 瀏覽:734
lol腳本2017 發布:2025-07-17 10:52:18 瀏覽:826
編譯條件有哪些 發布:2025-07-17 10:42:51 瀏覽:333
apache設置靜態內容緩存時間 發布:2025-07-17 10:32:32 瀏覽:294
蘋果6不能緩存的視頻 發布:2025-07-17 10:31:44 瀏覽:404
如何把文件夾共享 發布:2025-07-17 10:23:49 瀏覽:298
linux查看命令大全 發布:2025-07-17 10:21:24 瀏覽:976
安卓應用程序和蘋果手機哪個好 發布:2025-07-17 10:20:43 瀏覽:411
數據結構c語言試卷 發布:2025-07-17 10:12:09 瀏覽:380