1. 在Northwind数据库上建立一个视图 VOrders
代码
SET NOCOUNT ON;
USE Northwind;
GOIF OBJECT_ID('dbo.VOrders') IS NOT NULL
DROP VIEW dbo.VOrders;
GO
CREATE VIEW dbo.VOrders
ASSELECT O.OrderID, O.OrderDate, O.CustomerID, O.EmployeeID, O.ShipVia,
SUM(OD.Quantity) AS Qty,
CAST(SUM(OD.Quantity * UnitPrice * (1 - Discount)) AS DECIMAL(12, 2)) AS Value
FROM dbo.Orders AS O
JOIN [Order Details] AS OD
ON O.OrderID = OD.OrderID
GROUP BY O.OrderID, O.OrderDate, O.CustomerID, O.EmployeeID, O.ShipVia
GO
2. 计算整个表中数据的中位值 （MSSQL 2000的做法）
代码
SELECT
(
(SELECT MAX(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1)
+
(SELECT MIN(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value DESC) AS H2)
) / 2 AS Median;
3. 用子查询来查询每个员工的中位值（MSSQL 2000的做法）
代码
SELECT EmployeeID,
(
(SELECT MAX(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders AS O1
WHERE O1.EmployeeID = E.EmployeeID
ORDER BY Value) AS H1)
+
(SELECT MIN(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders AS O2
WHERE O2.EmployeeID = E.EmployeeID
ORDER BY Value DESC) AS H2)
) / 2 AS Median
FROM dbo.Employees AS E;
4.1 SQL 2005的做法，首先我们看看用来计算中位值的行的数据，这里用到了通用表（CTE）和Row_Number函数
代码
WITH OrdersRN AS
(
SELECT EmployeeID, Value,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
FROM dbo.VOrders
)
SELECT EmployeeID, Value, RowNum, Cnt
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2);
4.2 SQL 2005中，用通用表和Row_Number函数来计算中位值
代码
WITH OrdersRN AS
(
SELECT EmployeeID, Value,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
FROM dbo.VOrders
)
SELECT EmployeeID, AVG(Value) AS Median
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
GROUP BY EmployeeID;
关键词：中位值，中位值计算，Median，CTE，Row_Number