按组排列 [英] arrange as per group by

问题描述

- 情景



i有10个这样的值



590

800

800

289

800

590

478

123

800

$



i想要这样的操作



800

590

289

478

123

$





像大多数计数首先出现等等

--scenario

i have 10 values like this

590
800
800
289
800
590
478
123
800
600

i want the op like this

800
590
289
478
123
600


Like the most count comes first and so on

推荐答案

请先阅读我的评论。



如果您只想获取唯一值，请尝试：

Please, read my comment first.

If you want to fetch only unique values, please, try this:

DECLARE @tmp TABLE (MyNumber INT)

INSERT INTO @tmp (MyNumber)
SELECT 590
UNION ALL SELECT 800
UNION ALL SELECT 800
UNION ALL SELECT 289
UNION ALL SELECT 800
UNION ALL SELECT 590
UNION ALL SELECT 478
UNION ALL SELECT 123
UNION ALL SELECT 800
UNION ALL SELECT 600

--display distinct values
SELECT DISTINCT MyNumber
FROM @tmp

SELECT MyNumber, COUNT(MyNumber) AS CountOfMyNumber
FROM @tmp
GROUP BY MyNumber
--uncomment below line if you want to show only non-duplicated values
--HAVING COUNT(MyNumber)=1
--unocmment below line if you would like to order by number of items
--ORDER BY COUNT(MyNumber) DESC

首页查询结果：

Frist query result set:

MyNumber
123
289
478
590
600
800

第二个查询结果集：

Second query result set:

MyNumber	CountOfMyNumber
123		1
289		1
478		1
590		2
600		1
800		4

List<int> list = new List<int>();
list.Add(590);
list.Add(800);
list.Sort();

经过一些分析后我得到了解决方案......但似乎我做的很复杂......指南我，如果你得到优化的代码...

I got the solution after some analysis... but it seems i am doing little complicated... guide me if you get optimized code...

create table #tempcount(amount int)
insert into #tempcount (amount)
select 1
union all select 2
union all select 2
union all select 2
union all select 2
union all select 2
union all select 5
union all select 5
union all select 5

create table #tempfinal（row int，amount int）

create table #tempfinal(row int, amount int)

insert into #tempfinal
select row_number() over (partition by amount order by amount) as [row number],
amount
from #tempcount


select amount from #tempfinal
group by amount
having amount >0
order by max(row) desc

这篇关于按组排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！