table1【表1】t1_lsbh t1_flh
-------------------------------
xsfa123 001
xsfa123 002
sssssss 001
table2【表2】t2_lsbh t1_flh
-------------------------------
xsfa123 001
希望结果:t1_lsbh t1_flh
-------------------------------
xsfa123 002
sssssss 001 也就是表2存在表1的数据,则相同的表1不显示出来
求SQL语句
-------------------------------
xsfa123 001
xsfa123 002
sssssss 001
table2【表2】t2_lsbh t1_flh
-------------------------------
xsfa123 001
希望结果:t1_lsbh t1_flh
-------------------------------
xsfa123 002
sssssss 001 也就是表2存在表1的数据,则相同的表1不显示出来
求SQL语句
except
select * from table2
from table1 t1
where not exists(select 1 from table2 t2 where t1.t1_lsbh=t2.t2_lsbh and t1.t1_flh=t2.t1_flh)
FROM table1 t1
WHERE NOT EXIST (SELECT * FROM table2 t2 WHERE t1.t1_lsbh=t2.t1_lsbh AND t1.t1_flh=t2.t1_flh)
from t1 a left join t2 b
on a.t1_lsbh=b.t1_lsbh
and not exists(select 1 from t1 where t1_lsbh=a.t1_lsbh and t1_flh>a.t1_flh)
-------------------------------
xsfa123 001 21
xsfa123 002 33
sssssss 001 12
table2【表2】t2_lsbh t2_flh t2_sum
-------------------------------
xsfa123 001 2000
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([t1_lsbh] varchar(7),[t1_flh] varchar(3))
insert [table1]
select 'xsfa123','001' union all
select 'xsfa123','002' union all
select 'sssssss','001'
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([t2_lsbh] varchar(7),[t1_flh] varchar(3))
insert [table2]
select 'xsfa123','001'
---查询---
select *
from table1 t1
where not exists(select 1 from table2 t2 where t1.t1_lsbh=t2.t2_lsbh and t1.t1_flh=t2.t1_flh)---结果---
t1_lsbh t1_flh
------- ------
xsfa123 002
sssssss 001(2 行受影响)
from table1 as t1 left join table2 as t2
on t1.t1_lsbh=t2.t1_lsbh and t1.t1_flh=t2.t1_flh
where t2.t1_flh is null
if not object_id('t1') is null
drop table t1
Go
Create table t1([t1_lsbh] nvarchar(7),[t1_flh] nvarchar(3))
Insert t1
select N'xsfa123',N'001' union all
select N'xsfa123',N'002' union all
select N'sssssss',N'001'
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([t2_lsbh] nvarchar(7),[t1_flh] nvarchar(3))
Insert t2
select N'xsfa123',N'001'
Go
select a.*
from t1 a left join t2 b
on a.t1_lsbh=b.t2_lsbh
where not exists(select 1 from t1 where t1_lsbh=a.t1_lsbh and t1_flh>a.t1_flh)
/*
t1_lsbh t1_flh
------- ------
xsfa123 002
sssssss 001(2 個資料列受到影響)
*/
from table1 t1
where t1_lsbh not in (select t1_lsbh from table2 t2 where t1.t1_lsbh=t2.t2_lsbh and t1.t1_flh=t2.t1_flh)
drop table t1
Go
Create table t1([t1_lsbh] nvarchar(7),[t1_flh] nvarchar(3))
Insert t1
select N'xsfa123',N'001' union all
select N'xsfa123',N'002' union all
select N'sssssss',N'001'
Go
if not object_id('t2') is null
drop table t2
Go
Create table t2([t2_lsbh] nvarchar(7),[t1_flh] nvarchar(3))
Insert t2
select N'xsfa123',N'001'
Go
select *
from t1
where t1_lsbh not in (select t1_lsbh from t2 where t1.t1_lsbh=t2.t2_lsbh and t1.t1_flh=t2.t1_flh)
/*
t1_lsbh t1_flh
------- ------
xsfa123 002
sssssss 001(2 行受影响)
*/10L's table data
-------------------------------
xsfa123 001
xsfa123 002
sssssss 001
table2【表2】t2_lsbh t1_flh
-------------------------------
xsfa123 001
希望结果:t1_lsbh t1_flh
-------------------------------
xsfa123 002
sssssss 001 */CREATE TABLE table1
(
t1_lsbh VARCHAR(100),
t1_flh VARCHAR(100)
);CREATE TABLE table2
(
t1_lsbh VARCHAR(100),
t1_flh VARCHAR(100)
);GOINSERT INTO table1
SELECT 'xsfa123','001'
UNION ALL
SELECT 'xsfa123','002'
UNION ALL
SELECT 'sssssss','001'
INSERT INTO table2
SELECT 'xsfa123','001'
SELECT *
FROM table1 t1
WHERE NOT EXISTS
(
SELECT * FROM table2 t2 WHERE t1.[t1_lsbh]=t2.[t1_lsbh] AND t1.[t1_flh]=t2.[t1_flh]
)
from table1 t1
where not exists(
select 1 from table2 t2 where t1.t1_lsbh=t2.t2_lsbh and t1.t1_flh=t2.t1_flh
)