sql - 按多列中的最大值排序

Question

通过查看每行中的最大列值来对查询进行排序的最有效方法是什么？

例如:

Dan | 5 | 0 | 3
Moe | 0 | 9 | 2
Joe | 3 | 3 | 8

对该数据集进行排序应返回:Moe、Joe、Dan

我的数据集比上面的更复杂，所以我试图避免子查询与 group by max 的任何组合。

Answer 1

您可以使用CROSS APPLY:

SELECT *
FROM tab t
CROSS APPLY (SELECT MAX(col) c FROM (VALUES(t.col1),(t.col2),(t.col3)) sub(col)) s
ORDER BY s.c DESC;

DBFiddle Demo

输出:

┌──────┬──────┬──────┬──────┬───┐
│ name │ col1 │ col2 │ col3 │ c │
├──────┼──────┼──────┼──────┼───┤
│ Moe  │    0 │    9 │    2 │ 9 │
│ Joe  │    3 │    3 │    8 │ 8 │
│ Dan  │    5 │    0 │    3 │ 5 │
└──────┴──────┴──────┴──────┴───┘

编辑

Great answer. I don't suppose there's a way to do this for an arbitrary number of columns (without dsql) is there?

有一种不用动态SQL的方法:

SELECT DISTINCT t.*, c.s
FROM tab t
CROSS APPLY (VALUES(CAST((SELECT t.* for XML RAW) AS xml))) B(XMLData)
CROSS APPLY (SELECT MAX(a.value('.','INT') ) s
             FROM B.XMLData.nodes('/row')  AS C1(n)
             CROSS APPLY C1.n.nodes('./@*') AS C2(a)
             WHERE a.value('local-name(.)','varchar(100)') LIKE 'col%'
                     -- filtering based on name
                     -- it is also possible to JOIN and filter 
                     -- based on metadata like sys.columns
               ) C
ORDER BY s DESC;

DBFiddle Demo