SQL 添加行号（转）

select CAST(ROW_NUMBER() OVER(order by Name) AS varchar(32)) AS rowindex可以将行号取出来转换成varchar型的显示在页面上 1 ROW_NUMBER SELECT ProductID, Name, Price, ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRank FROM Products ProductID Name Price PriceRank 8 Desk 495.0000 1 10 Executive Chair 295.0000 2 9 Chair 125.0000 3 5 Mouse 14.9500 4 6 Mousepad 9.9900 5 11 Scissors 8.5000 6 4 Stapler 7.9500 7 3 Binder 1.9500 8 ... SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount, ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomer FROM vwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID Name DateOrdered TotalOrderAmount BestCustomer Bob 12/1/2005 12649.9900 1 Bob 12/19/2005 265.8500 2 Tito 12/22/2005 14.9500 1 Tito 12/18/2005 12.4400 2 Darren 1/2/2006 620.0000 1 Bruce 1/5/2006 14.9500 1 Bruce 1/4/2006 9.9900 2 Lee Ann 1/3/2006 8.5000 1 ... 2 RANK SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount, RANK() OVER (ORDER BY TotalOrderAmount DESC) AS BestCustomer FROM vwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID Name DateOrdered TotalOrderAmount BestCustomer Bob 12/1/2005 12649.9900 1 Darren 1/2/2006 620.0000 2 Bob 12/19/2005 265.8500 3 Tito 12/22/2005 14.9500 4 Bruce 1/5/2006 14.9500 4 Tito 12/18/2005 12.4400 6 Bruce 1/4/2006 9.9900 7 Lee Ann 1/3/2006 8.5000 8 ... 3 NTILE SELECT ProductID, Name, Price, NTILE(4) OVER (ORDER BY Price DESC) as Quartile FROM Produts ProductID Name Price Quartile 8 Desk 495.0000 1 10 Executive Chair 295.0000 1 9 Chair 125.0000 2 5 Mouse 14.9500 2 6 Mousepad 9.9900 3 11 Scissors 8.5000 3 4 Stapler 7.9500 4 3 Binder 1.9500 4