如何使用等级/加入以及一起使用

贾廷·古普塔（Jatin Gupta）

我在代码中使用了多个内部联接，并提供了等级，但是现在我想选择一个特定等级。那么如何在where语句中使用等级？

这是我的代码，但现在请帮助我进一步进行操作：

select [YEAR],
       [IDManufacturer],
       sum([TotalPrice]), 
       rank() over (order by sum(totalprice) desc) as sales_rank
       from [dbo].[DIM_DATE] 
       join [dbo].[FACT_TRANSACTIONS] 
       on [dbo].[FACT_TRANSACTIONS].Date = [dbo].[DIM_DATE].DATE
       join [dbo].[DIM_MODEL] 
       on [dbo].[DIM_MODEL].IDModel=[dbo].[FACT_TRANSACTIONS].IDModel
       where [YEAR] in (2009,2010) 
       group by IDManufacturer,[year]
       order by sum([TotalPrice]) desc

现在我只选择3级和4级。该怎么做？

谢卡·科拉

您可以选择sub-query或CTE，我建议尝试2种方法，然后查看性能更好的执行计划选择：

子查询

SELECT * FROM 
(select [YEAR],
       [IDManufacturer],
       sum([TotalPrice]) TotalPrice, 
       rank() over (order by sum(totalprice) desc) as sales_rank
from [dbo].[DIM_DATE] 
       join [dbo].[FACT_TRANSACTIONS] 
       on [dbo].[FACT_TRANSACTIONS].Date = [dbo].[DIM_DATE].DATE
       join [dbo].[DIM_MODEL] 
       on [dbo].[DIM_MODEL].IDModel=[dbo].[FACT_TRANSACTIONS].IDModel
where [YEAR] in (2009,2010) 
       group by IDManufacturer,[year]
) as SQ
Where sales_rank = 3 or sales_rank = 4
go

公用表表达式

; with CTE as

(select [YEAR],
       [IDManufacturer],
       sum([TotalPrice]) TotalPrice, 
       rank() over (order by sum(totalprice) desc) as sales_rank
from [dbo].[DIM_DATE] 
       join [dbo].[FACT_TRANSACTIONS] 
       on [dbo].[FACT_TRANSACTIONS].Date = [dbo].[DIM_DATE].DATE
       join [dbo].[DIM_MODEL] 
       on [dbo].[DIM_MODEL].IDModel=[dbo].[FACT_TRANSACTIONS].IDModel
where [YEAR] in (2009,2010) 
       group by IDManufacturer,[year]
)

SELECT * FROM CTE WHERE sales_rank = 3 or sales_rank = 4

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。