--CTE 语句（适用于MSSQL2005以后版本）

with cte_testNavi(Id,Name,Pid )

as

(

--这是查询语句

SELECT Id,Name,Pid FROM Navi

WHERE Name='汽车'

union all

--这是需要递归的部分，CTE 自身调用完成循环递归查找

SELECT a.Id,a.Name,a.Pid FROM Navi a

INNER JOIN

cte_testNavi b

ON

(a.Pid=b.Id)

)

select * from cte_testNavi

--要求根据商品分类取出每个商品下最贵的前两个物品

-- OVER( partition by c.ClassID order by i.Price DESC)over 开窗函数可以避免group by带来的不包含在子列的情况，

--但是开窗函数会返回多行结果，要根据使用情况去判定，比如这里我要按照ClassID商品类别ID进行分组

--RANK函数微软的定义是：返回结果集分区内每行的排名。一行的排名是一个加上所涉及行之前的行列数。

--比如这里我按Price价格去排序，取出每个分类下最贵的商品从上到下排序

SELECT * FROM

(

SELECT i.Price,i.CommodityName,c.ClassName,RANK() OVER( partition by c.ClassID order by i.Price DESC) rank2

FROM CommodityInfo i

INNER JOIN CommodityClass c

ON i.CommodityClass=c.ClassID

)R

WHERE rank2<=2;