T-SQL 取中位值

中位值：在每一个group中，值从小到大排列，中间那个值；或者中间那个值及后面的值的平均值。

如下表：

VehicleModelID	NewCarAmount
1	200
1	300
1	400
1	500
2	500
2	600
2	700

;WITH OrdersRN AS  
(  
  SELECT [VehicleModelID], NewCarAmount,  
    ROW_NUMBER() OVER(PARTITION BY [VehicleModelID] ORDER BY NewCarAmount) AS RowNum,  
    COUNT(*) OVER(PARTITION BY [VehicleModelID]) AS Cnt  
  FROM ##tblNewCarAmount  
)  

select [VehicleModelID],avg(NewCarAmount) NewCarAmount from (  
SELECT [VehicleModelID],NewCarAmount,RowNum,Cnt  
FROM OrdersRN o  
WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2)   
)a  
group by [VehicleModelID]  

查询结果为：

VehicleModelID	NewCarAmount
1	350
2	600