在 SQL 中的多个字段上选择最大值的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1183097/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best way to select max over multiple fields in SQL?
提问by allyourcode
The I kind of want to do is select max(f1, f2, f3). I know this doesn't work, but I think what I want should be pretty clear(see update 1).
我想要做的是选择 max(f1, f2, f3)。我知道这不起作用，但我认为我想要的应该很清楚（请参阅更新 1）。
I was thinking of doing select max(concat(f1, '--', f2 ...)), but this has various disadvantages. In particular, doing concat will probably slow things down. What's the best way to get what I want?
我正在考虑做 select max(concat(f1, '--', f2 ...))，但这有各种缺点。特别是，执行 concat 可能会减慢速度。获得我想要的东西的最佳方式是什么？
update 1: The answers I've gotten so far aren't what I'm after. max works over a set of records, but it compares them using only one value; I want max to consider several values, just like the way order by can consider several values.
更新 1：到目前为止我得到的答案不是我想要的。max 处理一组记录，但它只使用一个值来比较它们；我希望 max 考虑多个值，就像 order by 可以考虑多个值一样。
update 2: Suppose I have the following table:
更新2：假设我有下表：
id class_name order_by1 order_by_2 1 a 0 0 2 a 0 1 3 b 1 0 4 b 0 9
I want a query that will group the records by class_name. Then, within each "class", select the record that would come first if you ordered by order_by1ascending then order_by2ascending. The result set would consist of records 2 and 3. In my magical query language, it would look something like this:
我想要一个按 class_name 对记录进行分组的查询。然后，在每个“类”中，选择先order_by1升后order_by2升排序的记录。结果集将包含记录 2 和 3。在我神奇的查询语言中，它看起来像这样：
select max(* order by order_by1 ASC, order_by2 ASC) from table group by class_name
回答by AdaTheDev
Select max(val)
From
(
Select max(fld1) as val FROM YourTable
union
Select max(fld2) as val FROM YourTable
union
Select max(fld3) as val FROM YourTable
) x
Edit:Another alternative is:
编辑：另一种选择是：
SELECT
CASE
WHEN MAX(fld1) >= MAX(fld2) AND MAX(fld1) >= MAX(fld3) THEN MAX(fld1)
WHEN MAX(fld2) >= MAX(fld1) AND MAX(fld2) >= MAX(fld3) THEN MAX(fld2)
WHEN MAX(fld3) >= MAX(fld1) AND MAX(fld3) >= MAX(fld2) THEN MAX(fld3)
END AS MaxValue
FROM YourTable
回答by jpj625
I have one of these.
我有其中一个。
CREATE FUNCTION [dbo].[MathMax]
(
@a int,
@b int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
IF @a IS NULL AND @b IS NULL
RETURN 0
IF @a IS NULL
RETURN @b
IF @b IS NULL
RETURN @a
IF @a < @b
RETURN @b
RETURN @a
END
Then I can do SELECT dbo.MathMax(dbo.MathMax(MAX(f1), MAX(f2)), MAX(f3)) FROM T1
然后我可以做 SELECT dbo.MathMax(dbo.MathMax(MAX(f1), MAX(f2)), MAX(f3)) FROM T1
I believe this performs about the same as the CASE while being more readable, and performs much better than the multi-UNION (even the slightly more efficient UNION ALL).
我相信这与 CASE 的性能大致相同，同时更具可读性，并且比多 UNION 的性能要好得多（即使是稍微更高效的 UNION ALL）。
回答by allyourcode
Based on an answer I gave to another question: SQL - SELECT MAX() and accompanying field
基于我对另一个问题的回答：SQL - SELECT MAX() 和伴随的字段
To make it work for multiple columns, add more columns to the inner select's ORDER BY.
要使其适用于多列，请向内部选择的 ORDER BY 添加更多列。
回答by Augustus Kling
You would group by your class(could be multiple columns) and use a subselect within the column list that uses an ordinary order byin combination with limit 1.
您将按您的班级（可以是多列）进行分组，并在列列表中使用一个子选择，该子选择将普通order by与limit 1.
