SQL SERVER - 了解MIN（文本）的工作原理

I'm doing a little digging and looking for a explanation on how SQL server evaluates MIN(Varchar).

我正在进行一些挖掘，并寻找有关SQL服务器如何评估MIN（Varchar）的解释。

I found this remark in BOL: MIN finds the lowest value in the collating sequence defined in the underlying database

我在BOL中发现了这句话：MIN在底层数据库中定义的整理顺序中找到最低值

So if I have a table that has one row with the following values:

因此，如果我有一个包含以下值的行的表：

Data

AA
AB
AC

Doing a SELECT MIN(DATA) would return back AA. I just want to understand the why behind this and understand the BOL a little better.

执行SELECT MIN（DATA）将返回AA。我只是想了解这背后的原因并更好地理解BOL。

Thanks!

谢谢！

3 个解决方案

#1

It's determined by the collation (sort order). For most cultures the collation order is the same as the alphabetical order in the English alphabet so:

它由排序规则（排序顺序）决定。对于大多数文化，整理顺序与英文字母中的字母顺序相同，因此：

'AA' < 'AB'
'AA'<'AB'
'AA' < 'AC'
'AA'<'AC'
'AB' < 'AC'
'AB'<'AC'

Therefore 'AA' is the minimum value. For other cultures this may not hold. For example a Danish collation would return 'AB' as the minimum because 'AA' > 'AB'. This is because 'AA' is treated as equivalent to 'Å' which is the last letter in the Danish alphabet.

因此'AA'是最小值。对于其他文化，这可能不成立。例如，丹麦校对会将'AB'作为最小值返回，因为'AA'>'AB'。这是因为'AA'被视为等同于'Å'，这是丹麦字母表中的最后一个字母。

SELECT MIN(s COLLATE Danish_Norwegian_CI_AS) FROM table1;

min_s
AB

To get an "ordinary" sort order use the Latin1_General_Bin collation:

要获得“普通”排序顺序，请使用Latin1_General_Bin排序规则：

SELECT MIN(s COLLATE Latin1_General_Bin) FROM table1;

min_s
AA

To reproduce this result you can create this test table:

要重现此结果，您可以创建此测试表：

CREATE TABLE table1 (s varchar(100));
INSERT INTO table1 (s) VALUES ('AA'), ('AB'), ('AC');

#2

No, MIN is used in a SELECT statement that scans more than one line. It takes a column as an argument, and returns the "lowest" value (again, according to the collation sequence) found in that column.

不，MIN用于扫描多行的SELECT语句。它将一列作为参数，并返回该列中的“最低”值（同样，根据归类序列）。

Used without a GROUP BY clause, the result set will have a single row, and the value of MIN will be the lowest value found in that column. Used with a GROUP BY clause, the result set will have one row for each group and the value of MIN will be the lowest value in that column for any row in the group.

如果没有GROUP BY子句，结果集将只有一行，MIN的值将是该列中找到的最低值。与GROUP BY子句一起使用时，结果集将为每个组分配一行，MIN的值将是该列中任何行的最低值。

#3

min(x), where is a char (string) type -- char(), varchar(), nchar(), nvarchar(), finds the lowest value in the group, based on SQL's string comparison rules:

min（x），其中是char（字符串）类型 - char（），varchar（），nchar（），nvarchar（），根据SQL的字符串比较规则查找组中的最小值：

if two strings differ in length, the shorter is padded with SP characters (spaces) to the length of the longer.
如果两个字符串的长度不同，则较短的字符用SP字符（空格）填充到较长的字符串。
comparison proceeds left-to-right, character by character, according to the rule of the collation sequence in use.
根据使用的核对序列的规则，比较从左到右逐个字符地进行。
in comparisons, the value NULL compares lower than any non-null values (the ISO/ANSI SQL standard says that it is an implementation choice as to whether NULL collates lower or higher than any non-null value).
在比较中，值NULL比任何非空值都要低（ISO / ANSI SQL标准表明，对于NULL是否整理低于或高于任何非空值，它是一个实现选择）。

So, if you have a table

所以，如果你有一张桌子

create table foo
(
  myString varchar(16) not null ,
)

then running the query

然后运行查询

select min(myString) from foo

will give you the same result set as if you executed

将为您提供与执行时相同的结果集

set rowcount 1

select myString
from foo
order by myString

set rowcount 0

You are basically ordering the set in ascending sequence and selecting the first value. MAX(), or course, gives you the inverse, ordering the set in descending sequence and selecting the first value.

您基本上按升序排序并选择第一个值。 MAX（）或过程为您提供逆序，按降序排列集合并选择第一个值。

#1