如何从表中获得最高，然后是最低，然后是第二高，然后是第二低值等等

I've a question, how can I get the highest value, then the lowest value, then the second highest value from a table.

我有一个问题,我如何获得最高值,然后是最低值,然后是表格中的第二高值。

For example: in the table

例如:在表格中

Name        Value
----------------------
Apple        2
Pear         3
Pineapple    6
Mango        7
Kiwi         1

Result should look like this:

结果应如下所示:

Name           Value
-----------------------
Mango           7
Kiwi            1
Pineapple       6
Apple           2
Pear            3

Thanks!

3 个解决方案

#1

I'm assuming the tsqlt tag was meant to be tsql, and further that this implies that this is for SQL server:

我假设tsqlt标签本来是tsql,而且这意味着这是针对SQL服务器的:

;with Numbered as (
    select Name,Value,
        ROW_NUMBER() OVER (ORDER BY Value DESC) as rnDesc,
        ROW_NUMBER() OVER (ORDER BY Value ASC) as rnAsc
    from
        @t
), MixedNumbered as (
    select
        Name,Value,
        CASE WHEN rnDesc < rnAsc THEN rnDesc ELSE rnAsc END as rnFin,
        rnAsc,
        rnDesc
    from
        Numbered
)
select Name,Value from MixedNumbered order by rnFin,rnDesc

This works by finding the row numbers whilst considering the list sorted both highest-to-lowest and lowest-to-highest (in Numbered, rnDesc and rnAsc). We then take the lowest row number that was achieved when considering either of these orderings (MixedNumbered, rnFin).

这通过查找行号同时考虑从最高到最低和从最低到最高(在编号,rnDesc和rnAsc中)排序的列表来工作。然后,我们采用在考虑这些排序中的任何一个(MixedNumbered,rnFin)时获得的最低行数。

This should then, produce two rows with an rnFin equal to 1, two rows with it equal to 2, and so on; pairing the nth highest and nth lowest rows until we reach the middle of the set.

这应该产生两行,其中rnFin等于1,两行等于2,依此类推;配对第n个最高行和第n个最低行,直到我们到达集合的中间。

We then use this to sort the final result set - but use the position obtained by considering the values sorted highest-to-lowest (rnDesc) as the tie breaker between each pair of rows with the same rnFin value. This means, for each pair, that the higher valued row will appear first.

然后我们使用它来对最终结果集进行排序 - 但是使用通过考虑从最高到最低排序的值(rnDesc)获得的位置作为具有相同rnFin值的每对行之间的平局断开器。这意味着,对于每一对,较高值的行将首先出现。

To reverse the result (lowest first, then highest, the second lowest, second highest, etc), we need only change the final ORDER BY clause to rnFin,rnAsc.

要反转结果(最低,然后最高,第二低,第二高等),我们只需要将最终的ORDER BY子句更改为rnFin,rnAsc。

#2

This assigns the number 2 to the largest row, 3 to the smallest, 4 to the second largest, and so on.

这将数字2分配给最大的行,3分配给最小的行,4分配给第二大的行,依此类推。

select  *
from    (
        select  1 + 2 * row_number() over (order by Value asc) as rnAsc
        ,       2 * row_number() over (order by Value desc) as rnDesc
        ,       t1.*
        from    Table1 t1
        ) SubQueryAlias
order by
        case
        when rnDesc < rnAsc then rnDesc
        else rnAsc
        end

Example at SQL Fiddle.

SQL Fiddle的例子。

#3

A great question! Please check my try:

一个很好的问题!请检查我的尝试:

SELECT Name,Value
FROM(
    SELECT *, MAX(Rnum) OVER() mx, MAX(Rnum) OVER()/2.0 hf FROM(
        SELECT *, ROW_NUMBER() OVER(ORDER BY value DESC) Rnum From @tbl
    )x
)xx
ORDER BY CASE WHEN Rnum-hf<=0 THEN Rnum ELSE mx-Rnum+1 END, Rnum

#1