27,580
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Tname varchar(3),Cname varchar(8),Number int,Class varchar(9))
insert into #T
select 'Jim','Maths',60,'计算机1班' union all
select 'Jim','Maths',120,'计算机2班' union all
select 'Jim','Maths',180,'计算机3班' union all
select 'Jim','physics',180,'计算机3班' union all
select 'Jim','physics',120,'计算机2班' union all
select 'bob','Maths',100,'自动化1班' union all
select 'bob','computer',120,'计算机2班'
select Tname,Cname,Number,Class,(case c when 1 then 1 else case when Number=m then 1 else 0.7 end end)cs from
(
select *,count(1)over(partition by TName,CName)c, max(Number)over(partition by TName,CName)m from #T
) t
/*
Tname Cname Number Class cs
----- -------- ----------- --------- -----------
bob computer 120 计算机2班 1.0
bob Maths 100 自动化1班 1.0
Jim Maths 60 计算机1班 0.7
Jim Maths 120 计算机2班 0.7
Jim Maths 180 计算机3班 1.0
Jim physics 180 计算机3班 1.0
Jim physics 120 计算机2班 0.7
*/
create table tb(Tname varchar(10) , Cname varchar(10) , Number int,Class varchar(10))
insert into tb values('Jim' ,'Maths' , 60 ,'计算机1班')
insert into tb values('Jim' ,'Maths' , 120 ,'计算机2班')
insert into tb values('Jim' ,'Maths' , 180 ,'计算机3班')
insert into tb values('Jim' ,'physics', 180 ,'计算机3班')
insert into tb values('Jim' ,'physics', 120 ,'计算机2班')
insert into tb values('bob' ,'Maths' , 100 ,'自动化1班')
insert into tb values('bob' ,'computer' ,120 ,'计算机2班')
go
select n.* , xs = 0.7 from tb n where not exists(select 1 from (select tname,cname,number from tb t where number = (select max(number) from tb where tname = t.tname and cname = t.cname)) m where tname = n.tname and cname = n.cname and number = n.number)
union all
select t.* , xs = 1 from tb t where number = (select max(number) from tb where tname = t.tname and cname = t.cname)
order by tname , cname
drop table tb
/*
Tname Cname Number Class xs
---------- ---------- ----------- ---------- ----
bob computer 120 计算机2班 1.0
bob Maths 100 自动化1班 1.0
Jim Maths 180 计算机3班 1.0
Jim Maths 60 计算机1班 .7
Jim Maths 120 计算机2班 .7
Jim physics 120 计算机2班 .7
Jim physics 180 计算机3班 1.0
(所影响的行数为 7 行)
*/