从多列中查找最大值、第二次最大值和最小值

Question

我有一个表，列的长度，宽度，高度，我想找出，在三列中，最大将成为长度，第二最大将成为宽度和最小将高度。

table name dimension

length  width   height
5.60    3.70    0.90
13.50   6.54    3.50
14.33   7.95    3.86
6.42    6.69    7.95
12.00   10.00   9.00
5.60    3.70    3.70

我尝试了案例逻辑，并找到了最大，最小，但第二次最大没有得到想要的结果。

select length = case
                    when dimension.[length] >= dimension.width and dimension.[length] >= dimension.height then dimension.[length]
                    when dimension.[width] >= dimension.[length] and dimension.[width] >= dimension.height then dimension.[width]
                    when dimension.[height] >= dimension.[length] and dimension.[height] >= dimension.width then dimension.[height]
                end,
        width = case 
                    when (dimension.[length] <= dimension.width and dimension.[length] >= dimension.height)  then dimension.[length] 
                    when (dimension.[width] <= dimension.[length] and dimension.[width] >= dimension.height) then dimension.[width]
                    when (dimension.[height] <= dimension.[length] and dimension.[height] >= dimension.width )then dimension.[height]
                end,
        height = case 
                    when dimension.[length] <= dimension.width and dimension.length<= dimension.height then dimension.[length]
                    when dimension.[width] <= dimension.[length] and dimension.[width] <= dimension.height then dimension.[width]
                    when dimension.[height] <= dimension.[length] and dimension.[height] <= dimension.width then dimension.[height]
                end

from dimension

在返回长度、宽度和高度时，应正确显示第二大值。

这样的预期产出

length  width   height
5.60    3.70    0.90
13.50   6.54    3.50
14.33   7.95    3.86
7.95    NULL    6.42
12.00   10.00   9.00
5.60    3.70    3.70

但在某些情况下，我得到的是宽度列中的空值。

Answer 1

For Server 2012+:

使用ORDER BY与OFFSET和FETCH一起获得预期输出的另一种可能方法

表：

CREATE TABLE #Dimensions (
    [length] numeric(10, 2),  
    [width] numeric(10, 2),   
    [height] numeric(10, 2)
)
INSERT INTO #Dimensions
    ([length], [width], [height])
VALUES
    (5.60,    3.70,    0.90),
    (13.50,   6.54,    3.50),
    (14.33,   7.95,    3.86),
    (6.42,    6.69,    7.95),
    (12.00,   10.00,   9.00),
    (5.60,    3.70,    3.70)

声明：

SELECT c.*
FROM #Dimensions d
CROSS APPLY (
    SELECT 
        [length] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY),
        [width] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY),
        [height] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY)
) c

输出：

----------------------
length  width   height
----------------------
5.60    3.70    0.90
13.50   6.54    3.50
14.33   7.95    3.86
7.95    6.69    6.42
12.00   10.00   9.00
5.60    3.70    3.70

For Server 2008+:

当不支持带有OFFSET和FETCH的FETCH时，使用ROW_NUMBER()的方法也是一种解决方案：

SELECT 
    [length] = c1.N,
    [width] = c2.N,
    [height] = c3.N
FROM #Dimensions d
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c1
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c2
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c3
WHERE (c1.RN = 1) AND (c2.RN = 2) AND (c3.RN = 3)

Answer 2

希望这对你有用：

select
    length = (select max(v) from (values (length), (width), (height)) as t(v)),
    width = (select (sum(v) - max(v) - min(v)) from (values (length), (width), (height)) as t(v)),
    height = (select min(v) from (values (length), (width), (height)) as t(v))
from dimension

Answer 3

-逻辑

select * from sample;

 length | width | height 

 6 |     4 |      1
14 |     7 |      4
---------------------------------
This query been tried in Postgres   (U can use rownum in oracle in place of grpno)

with basedata as  (select floor(random()*100) grpno,length,width ,height from sample)  
select max(measurmnt) as length,min(measurmnt) height,sum(measurmnt)-( max(measurmnt)+min(measurmnt)) as width from 
 ( select grpno,length measurmnt from basedata union all  
         select grpno,width from basedata  union all 
 select grpno,height from basedata order by 1
) as outerq
group by grpno;
 length | height | width 
--------+--------+-------
      6 |      1 |     4
     14 |      4 |     7