现有如下数据库
fid pid coadcladcon
RAA0370AA01 PRAA0370 1
RAA0370AA02 PRAA0370 2
RAA0370AA03 PRAA0370 3
RAA0370AA04 PRAA0370 4
RAA0370AA05 PRAA0370 5
RAA0370AA06 PRAA0370 6
RAA0370AB01 PRAA0371 1
RAA0370AB02 PRAA0371 2
RAA0370AB03 PRAA0371 3
RAA0370AB04 PRAA0371 4
RAA0370AB05 PRAA0371 5
RAA0370AB06 PRAA0371 6
RAA0370AB07 PRAA0371 7
现在要根据pid来取得相同pid的coadcladcon中位值，
得到一个新的临时表，如下数据：
pid coadcladcon
PRAA0370 4
PRAA0371 4
------解决方案--------------------
create table tb(pid varchar(10),coadcladcon int)
insert into tb
select 'PRAA0370',1 union all
select 'PRAA0370',2 union all
select 'PRAA0370',3 union all
select 'PRAA0370',4 union all
select 'PRAA0370',5 union all
select 'PRAA0370',6 union all
select 'PRAA0371',1 union all
select 'PRAA0371',2 union all
select 'PRAA0371',3 union all
select 'PRAA0371',4 union all
select 'PRAA0371',5 union all
select 'PRAA0371',6 union all
select 'PRAA0371',7
with cte as
(
select no=row_number() over(partition by pid order by getdate()),*,
num=(select count(*) from tb where pid=a.pid)
from tb a
)
select pid,coadcladcon
from (select no1=row_number() over(partition by pid order by abs(no*1./(num+1)-0.5)),* from cte) t
where no1=1