有id和数量两列，求每个id的中位数，我写了一个，感觉不是很好似的，也没想起来又啥好写法，贴在这，看看大家有啥好写法没有
--测试数据
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
