22,209
社区成员
发帖
与我相关
我的任务
分享
select * from a where not exists(select 1 from b where a.[学号]=[学号] and a.[分数]=[分数])
union all
select * from b where not exists(select 1 from a where [学号]=b.[学号] and [分数]=b.[分数])
create table a([学号] varchar(10),[分数] int)
insert a
select '001' ,90 union all
select '002', 80 union all
select '003' ,70 union all
select '004' ,89
create table b([学号] varchar(10),[分数] int)
insert b
select '001', 90 union all
select '002', 90
go
select o.[学号],o.[分数],u.[分数] from
(
select a.[学号],a.[分数] from a
where not exists (select 1 from b where a.[学号]=b.[学号] and a.[分数]=b.[分数])
) as o
left join b as u on o.[学号]=u.[学号]
/*
(所影响的行数为 3 行)
学号 分数 分数
--- --- ----
002 80 90
003 70 NULL
004 89 NULL
*/
go
drop table a,b
declare @表A table (学号 varchar(3),分数 int)
insert into @表A
select '001',90 union all
select '002',80 union all
select '003',70 union all
select '004',89
declare @表B table (学号 varchar(3),分数 int)
insert into @表B
select '001',90 union all
select '002',90
SELECT ISNULL(a.学号,b.学号) AS 学号,a.分数,b.分数
FROM @表A AS a
FULL JOIN
@表B AS b ON a.学号=b.学号
WHERE NULLIF(a.分数,b.分数) IS NOT NULL
/*
学号 分数 分数
002 80 90
003 70 NULL
004 89 NULL
*/
declare @表A table (学号 varchar(3),分数 int)
insert into @表A
select '001',90 union all
select '002',80 union all
select '003',70 union all
select '004',89
declare @表B table (学号 varchar(3),分数 int)
insert into @表B
select '001',90 union all
select '002',90
select a.学号 ,
a.分数 as 分数a ,
b.分数 as 分数b
from @表a a
left join @表b b on a.学号 = b.学号
where isnull(a.分数, 0) <> isnull(b.分数, 0)
/*
学号 分数a 分数b
---- ----------- -----------
002 80 90
003 70 NULL
004 89 NULL
*/