日期:2014-05-16 浏览次数:20604 次
SELECT *
FROM (
SELECT DENSE_RANK() AS NUM,*
FROM TB
)
WHERE NUM=3
USE AdventureWorks2012;
GO
WITH a AS(
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,DENSE_RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
--WHERE i.LocationID BETWEEN 3 AND 4
)
SELECT * FROM a WHERE a.productid BETWEEN 367 AND 389
GO
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v6.5.278
* Time: 2014/4/11 星期五 上午 11:51:25
************************************************************/
USE AdventureWorks2012;
GO
WITH a AS(
SELECT i.ProductID,
p.Name,
i.LocationID,
i.Quantity,
ROW_NUMBER() OVER(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS
RANK,
COUNT(1) OVER(PARTITION BY i.LocationID) AS COUNT
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
--WHERE i.LocationID BETWEEN 3 AND 4
)
SELECT CASE
WHEN CONVERT(DECIMAL(18, 2), [rank]) / [COUNT] <= 0.2 THEN N'1等'
WHEN CONVERT(DECIMAL(18, 2), [rank]) / [COUNT] <= 0.8 AND CONVERT(DECIMAL