27,582
社区成员




--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] INT,tcount DECIMAL(18,2))
Insert #T
select 1,1 union all
select 1,2 union all
select 1,3 union ALL
select 1,4 union ALL
select 1,5 union all
select 2,6 union all
select 2,7 union all
select 3,8
Go
--测试数据结束
;WITH cte AS (
SELECT id,tcount,
COUNT(1) OVER (PARTITION BY id) cou,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY tcount) rn
FROM #T
)
SELECT id,AVG(cte.tcount) AS 中位数 FROM cte WHERE rn = cte.cou/2+1 AND cte.cou%2=1 GROUP BY cte.id
UNION ALL
SELECT a.id,
sum(a.tcount + ISNULL(b.tcount, 0))/2
FROM cte a
LEFT JOIN cte b
ON a.id = b.id
WHERE a.rn = a.cou/2
AND a.cou % 2 = 0
AND b.rn = a.rn + 1
GROUP BY a.id
ORDER BY id
;WITH cte AS (
SELECT id,tcount,
COUNT(1) OVER (PARTITION BY id) cou,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY tcount) rn
FROM #T
)
SELECT id,avg(tcount) as 中位数
FROM cte WHERE rn between ceiling(cou*1.0/2) and ceiling((cou+1)*1.0/2)
group by id
select id,中位数=avg(tcount)
from #T
group by id