按最大值对多列进行排序

Question

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

例如：

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

排序这个数据集应该返回: Moe，Joe，Dan

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

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演示

输出：

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

编辑

回答得好。我不认为对任意数量的列(没有dsql)有这样的方法吗？

有一种不使用动态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演示

Answer 2

我几乎从不在数据库上排序。在这种情况下，排序是复杂的，并且比您提供的模型更复杂，我通常会将数据从数据库中提取出来，然后用代码对其进行排序。

当我这样做的时候，我总是得到更多的奖励。在内存中对8000000行进行排序比在数据库中排序要便宜得多。由于您的数据模型很复杂，编写一种算法来对其进行排序比编写sql更灵活--您可以编写抵抗模式更改的代码，并将继续遵守您的规则。