SQL SERVER-了解MIN(text)的工作方式 [英] SQL SERVER - Understanding how MIN(text) works

问题描述

我正在做一些挖掘工作，并寻找有关SQL Server如何评估MIN(Varchar)的解释.

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

我在BOL中发现了这一点:MIN找到了基础数据库中定义的整理顺序中的最小值

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

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

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

Data

AA
AB
AC

执行SELECT MIN(DATA)将返回AA.我只想了解其背后的原因，并更好地理解BOL.

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.

谢谢！

推荐答案

由

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'< "AC"
• 'AB'< "AC"

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

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.

SELECT MIN(s COLLATE Danish_Norwegian_CI_AS) FROM table1;

min_s
AB

要获取常规"排序顺序，请使用Latin1_General_Bin归类:

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

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');

这篇关于SQL SERVER-了解MIN(text)的工作方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！