Cacher is the code snippet organizer for pro developers

-- http://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server
-- 分组
SELECT * FROM (
SELECT
   competeType,
   AVG(entireBonus) AS mid
FROM
(
   SELECT
      competeType,
      entireBonus,
      ROW_NUMBER() OVER (
         PARTITION BY competeType 
         ORDER BY entireBonus ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY competeType 
         ORDER BY entireBonus DESC) AS RowDesc
   FROM compete SOH WHERE SOH.entireBonus > 0 AND SOH.competeTypeLevel = 1
) x
WHERE 
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY competeType) t
WHERE t.mid IS not NULL
ORDER BY t.mid desc

-- 不分组
SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median