34,588
社区成员
发帖
与我相关
我的任务
分享
select m.* from
(select * from tb where 录入人员 = '小王') m
checksum(*) not in
(select * from tb where 录入人员 = '小李') n
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([序号] int,[姓名] nvarchar(2),[分数1] int,[分数2] int,[录入人员] nvarchar(2))
Insert #T
select 1,'张三',7,10,'小王' union all
select 2,'李四',10,3,'小王' union all
select 3,'张三',10,10,'小李' union all
select 4,'李四',10,3,'小李'
Go
select *from #t a,#t b
where a.录入人员='小王' and b.录入人员='小李'
and a.姓名=b.姓名 and (a.分数1<>b.分数1 or a.分数2<>b.分数2)
--> 测试数据
declare @tb table ([seq] int,[cname] nvarchar(2),[score] int,[score2] int,[op] nvarchar(2))
Insert into @tb
select 1,'张三',7,10,'小王' union all
select 2,'李四',10,3,'小王' union all
select 3,'张三',10,10,'小李' union all
select 4,'李四',10,3,'小李'
Select * from @tb a
left join @tb b on a.[cname] = b.[cname] and b.[op] ='小李'
where a.[op] ='小王' and ((a.[score] != b.[score]) or (a.[score2] != b.[score2]))
/*
seq cname score score2 op seq cname score score2 op
----------- ----- ----------- ----------- ---- ----------- ----- ----------- ----------- ----
1 张三 7 10 小王 3 张三 10 10 小李
(1 row(s) affected)
*/
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (序号 int,姓名 varchar(11),分数1 int,分数2 int,录入人员 varchar(11))
insert into #T
select 1,'张三',7,10,'小王' union all
select 2,'李四',10,3,'小王' union all
select 3,'张三',10,10,'小李' union all
select 4,'李四',10,3,'小李'
--> 2005
select 姓名,分数1,分数2 from #T where 录入人员='小王'
except
select 姓名,分数1,分数2 from #T where 录入人员='小李'
/*
姓名 分数1 分数2
----------- ----------- -----------
张三 7 10
*/
--> 2000
select * from #T as t where 录入人员='小王' and not exists (select 1 from #T where 录入人员='小李' and 姓名=t.姓名 and 分数1=t.分数1 and 分数2=t.分数2)
/*
序号 姓名 分数1 分数2 录入人员
----------- ----------- ----------- ----------- -----------
1 张三 7 10 小王
*/
select a.* from a not exists (select * from a group by 录入人员)
create table # (序号 int ,姓名 nvarchar(100),分数1 int,分数2 int,录入人 nvarchar(100))
insert into # select 1,'张三',7,10,'小王' union select 2,'李四',10,3,'小王'
union
select 3,'张三',10,3,'小李' union select 4,'李四',10,3,'小李'
select * from #
select * from # A where 录入人='小王' and exists(select * from # B where B.录入人='小李' and A.姓名=B.姓名 and (A.分数1!=B.分数1 or A.分数2!=B.分数2))