Sql Server 通过cross apply进行分类查询

先准备下数据，一张产品类别表，一张产品表。

类别表，ProductCategory(主键为ID)

 

 产品表，Product(主键为ID,外键为ProductCategoryID)

一.使用cross apply
　　如果想查询每个类别价格最高的三个产品分别是什么。
　　下面是使用cross apply进行查询

　　select a.cateName as '产品类别',b.Name as '产品名称',b.PriceSale as '价格',b.* from (
　　　　select ID as 'cateid',Name as 'cateName', 3 as 'num' from ProductCategory where IsDeleted=0
　　) as a cross apply (select top(a.num) * from Product where ProductCategoryID=a.cateID order by PriceSale desc) as b order by b.ProductCategoryID,b.PriceSale desc

　　查询结果，b.*是product表的所有例。

　　

 

　　这里有几点需要注意
　　1.top(a.num)这是为了控制排名靠前的几个商品，也可以不加不定义num直接写用数字替换a.num，这里引用上面定义的num是因为这么做可以根据类别分别显示不同行数的数据。
　　例：　新春礼品类别显示价格最高的产品，其它类别显示排名前三的产品。

　　select a.cateName as '产品类别',b.Name as '产品名称',b.PriceSale as '价格',b.* from (
　　　　select ID as 'cateid',Name as 'cateName'
　　　　　　,case Name
　　　　　　when '新春礼品' then 1
　　　　　　else 3 end as 'num'
　　　　from ProductCategory where IsDeleted=0
　　) as a cross apply (select top(a.num) * from Product where ProductCategoryID=a.cateID order by PriceSale desc) as b order by b.ProductCategoryID,b.PriceSale desc

　　

　　2.因为是要价格最高的前三个产品，因此需要对product表的pricesales进行倒序，反之如果要获取最低则改为asc

　　select a.cateName as '产品类别',b.Name as '产品名称',b.PriceSale as '价格',b.* from (
　　　　select ID as 'cateid',Name as 'cateName', 3 as 'num' from ProductCategory where IsDeleted=0
　　) as a cross apply (select top(a.num) * from Product where ProductCategoryID=a.cateID order by PriceSale desc) as b order by b.ProductCategoryID,b.PriceSale desc

 

二.上面的分类查询如果改为查找排名最高的前三个价格，那么咋办，特别是价格重复的情况。　　

　　

　　比如以上价格排名前三的产品，如果还用之前的排序方式，我们只能拿到：时令芦笋，罗马生菜，山泉竹芥菜，这三个产品。
　　但根据价格排名前三的价格是16,15,12，因此我们真正想要的则是以下产品列表
　　

 　　整理之后的SQL如下。　　

　　select a.cateName,b.Name as '产品名称',b.PriceSale as '价格',b.*
　　from
　　(
　　　　select ID as 'cateid',Name as 'cateName'
　　　　,case Name
　　　　when '套餐' then 1
　　　　else 3 end as 'num'
　　　　from ProductCategory where IsDeleted=0
　　) as a
　　cross apply
　　(
　　　　select * from Product
　　　　where ProductCategoryID=a.cateID and IsDeleted=0 and
　　　　PriceSale in
　　　　(
　　　　　　select top(a.num) PriceSale from Product where ProductCategoryID=a.cateID and IsDeleted=0 group by PriceSale order by PriceSale desc
　　　　)
　　) as b order by b.ProductCategoryID,b.PriceSale desc
　　
　　1.第一个红色部分是为了确保数据显示时，只显示相关类别。
　　2.第二个红色是为了确保获取每个类别产品价格前三名，如果不加这个限制就是所有类别产品价格前三名。
结果如下：

　　

 

 　　之前有看到别人好像是用开窗函数也可以查出来，但是时间太久，忘记怎么写了，如果有更优写法欢迎拍砖。