SQL中的排名功能 - 以成本方式显示材料。 [英] Rank function in SQL - To display the Material in Cost wise.

问题描述

亲爱的朋友们，



我想在订单中显示数据。请检查以下内容，例如：

Dear Friends,

I want to display the data in an order. Kindly check the below eg.

Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o

Select * from #AP order by cast (RATES_MASTER as int) desc, category desc

Drop Table #AP

它给出了结果作为描述。按价格。

但实际上我想显示成本高的材料，然后按材料分组。

例如：计算机具有高速率，因此它应显示为第一所有价格的类别，然后它应该采取笔记本电脑类别。



请帮助我，提前谢谢。

It give me the result as Desc. by Rates.
But actually I want to display the Material which cost is High and then group by Material.
Eg: Computer has high rate, so it should display as first category with all the rates, and then it should take the Laptop category.

Kindly help me on this, Thanks in advance.

推荐答案

我的解决方案涉及一个临时表：

Hi,
my solution involves a temp table:

Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o
 
CREATE TABLE #OrderdCats
(
Cat NVARCHAR(100) ,
max_rat int
);

insert into #OrderdCats
Select Category, MAX(cast(Rates_Master As int)) from #AP  Group by Category Order By MAX(cast(Rates_Master As int)) DESC;
 
Select a.* From #AP a, #OrderdCats o WHERE a.Category = o.Cat Order By o.max_rat desc, cast(a.Rates_Master As int) desc;

DROP TABLE #OrderdCats
Drop Table #AP

您可以尝试以下查询...

Hi,

Can you try like below Query...

Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o
 
SELECT M.Category, M.RATES_MASTER
FROM #AP M
INNER JOIN (SELECT TOP 100 PERCENT T.Category, T.RATES_MASTER
			FROM (Select ROW_NUMBER()OVER (PARTITION BY Category ORDER BY CAST(RATES_MASTER AS INT) DESC) 'Rno', Category,RATES_MASTER 
			 FROM #AP ) T
			 WHERE T.Rno =1 
			 ORDER BY CAST(RATES_MASTER AS INT) DESC
			) S ON S.Category=M.Category 
ORDER BY CAST(S.RATES_MASTER AS INT) DESC, CAST(M.RATES_MASTER AS INT) DESC
 
Drop Table #AP

-- Note :I also used same like Solution 2, but with out 2nd Temp Table

问候，

GVPrabu

Regards,
GVPrabu

这篇关于SQL中的排名功能 - 以成本方式显示材料。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！