如何在sql server中使用字段值对记录进行排序 [英] How to sort record with field value in sql server

问题描述

大家好，



我有一个名为Result的表，结果表包含以下列。



表 - 结果



列 - > ResultId，CategoryId，WeightFrom，PlayerName，Result



和用户在此表中插入记录。

Hi Everyone,

I have a table called Result and result table contains following columns.

Table -- Result

Columns--> ResultId, CategoryId, WeightFrom, PlayerName, Result

and user insert the record in this table like this.

ResultId | CategoryId | WeightFrom | PlayerName | Result
1          1           20.0          Ashu        Silver
2          1           20.0          Pawan       Gold
3          1           22            Mahesh      Bronze
4          1           23            Ram         Bronze
5          2           23.00         Rajesh      Bronze
6          2           22.00         Rakesh      Gold
7          2           20.00         Vikas       Silver
8          2           21            Suraj       Bronze

现在我的问题是我想使用categoryId对记录进行排序， WeightFrom，结果基础



但主要问题是当我对这样的记录进行排序时。



选择PlayerName，结果排序按CategoryId，WeightFrom，结果asc



输出为：



Ashu Silver

Pawan Gold

mahesh铜牌



但是我想按这样排序记录，



Pawan Gold

Ashu S. ilver

Mahesh Broze

Ram Bronze



我想首先展示金，银，铜唱片但类别或明智的重量。



请帮我解决这个问题。

Now my question is I want to sort record using categoryId,WeightFrom,Result base

but main issue is that when i sort the record like this.

Select PlayerName,Result from Result order by CategoryId,WeightFrom, Result asc

Output is:

Ashu Silver
Pawan Gold
mahesh Bronze

But i want to sort record like this,

Pawan Gold
Ashu Silver
Mahesh Broze
Ram Bronze

I want to display first Gold, Silver, Bronze Record but category or weight from wise.

Please help me what i do for this.

推荐答案

这可以是通过CASE .. WHEN ..实现如下：

This can be achieved through "CASE .. WHEN.." like following

ORDER BY CASE WHEN Result='Gold' THEN 1 WHEN Result='Silver' THEN 2 ELSE 3 END,CategoryId,WeightFrom

例如：

For example :

SELECT * FROM
(
	SELECT 1 ResultId,1 CategoryId,20.0 WeightFrom,'Ashu' PlayerName,'Silver' Result
	UNION
	SELECT 2,          1,           20.0,          'Pawan',       'Gold'
	UNION
	SELECT 3,          1,           22,            'Mahesh',      'Bronze'
	UNION
	SELECT 4,          1,           23,            'Ram',         'Bronze'
	UNION
	SELECT 5,          2,           23.00,         'Rajesh',      'Bronze'
	UNION
	SELECT 6,          2,           22.00,         'Rakesh',      'Gold'
	UNION
	SELECT 7,          2,           20.00,         'Vikas',       'Silver'
	UNION
	SELECT 8,          2,           21,            'Suraj',       'Bronze'
) AS T
ORDER BY CASE WHEN Result='Gold' THEN 1 WHEN Result='Silver' THEN 2 ELSE 3 END,CategoryId,WeightFrom

结果如下：

Result will be like:

ResultId	CategoryId	WeightFrom	PlayerName	Result
2	          1	           20.00	Pawan	      Gold
6	          2	           22.00	Rakesh	      Gold
1	          1	           20.00	Ashu	      Silver
7	          2	           20.00	Vikas	      Silver
3	          1	           22.00	Mahesh	      Bronze
4	          1	           23.00	Ram	      Bronze
8	          2	           21.00	Suraj	      Bronze
5	          2	           23.00	Rajesh	      Bronze

现在您可以根据您的要求过滤数据，可能正在使用一些排名功能。

如果需要任何进一步的帮助，请告诉我们：）

Now you can filter the data as per your requirement, may be using some ranking functions.
In case of any further assistance is required, please let me know :)

您可以使用<$ c进行自定义排序您的 ORDER BY 中的$ c> CASE ： http://sqlandme.com/2013/11/18/ sql-server-custom-order-in-order-by-clause / [ ^ ]

You can custom sort with CASE in your ORDER BY: http://sqlandme.com/2013/11/18/sql-server-custom-sorting-in-order-by-clause/[^]

这篇关于如何在sql server中使用字段值对记录进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！