select sum(CB)/2
from TA a
where CB in(
select top 2 CB from TA
where CA=a.CA
order by CB desc)
group by CA
having count(*)>1
from TA a
where CB in(
select top 2 CB from TA
where CA=a.CA
order by CB desc)
group by CA
having count(*)>1
from
(
select * from TA a
where CB in (select top 2 CB from TA where CA = a.CA order by CB desc)
) aa
group by CA
having count(*) >=2
create table TA(CA varchar(10),CB decimal(10,1))
insert TA select 'A',1
union all select 'A',2
union all select 'A',3
union all select 'A',4
union all select 'B',5
union all select 'B',4
union all select 'B',3
union all select 'B',3
union all select 'C',6
go--查询
select CA,CB=sum(CB)/2
from TA a
where CB in(
select top 2 CB from TA
where CA=a.CA
order by CB desc)
group by CA
having count(*)>1
go--删除测试
drop table TA/*--测试结果CA CB
---------- ----------------------------------------
A 3.500000
B 4.500000(所影响的行数为 2 行)
--*/
from (select * from TA aa
where (select count(*) from TA where CA=aa.CA)>1 and
(select count(*) from TA where CA=aa.CA and CB>=aa.CB)>1) bb
group by bb.CA
select CA,CB=cast(sum(CB)/2.0 as decimal(10,1))
from TA a
where CB in(
select top 2 CB from TA
where CA=a.CA
order by CB desc)
group by CA
having count(*)>1