SQL “SELECT DISTINCT”忽略不同的情况
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2644851/
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
"SELECT DISTINCT" ignores different cases
提问by powerbar
I have the problem, that MSSQL Server 2000 should select some distinct values from a table (the specific column is of the nvarchar type). There are the sometimes the same values, but with different cases, for example (pseudocode):
我有一个问题，MSSQL Server 2000 应该从表中选择一些不同的值（特定列是 nvarchar 类型）。有时有相同的值，但情况不同，例如（伪代码）：
SELECT DISTINCT * FROM ("A", "a", "b", "B")
would return
会回来
A,b
But I do want (and do expect)
但我确实想要（并且确实期待）
A,a,b,B
because they actually are different values.
因为它们实际上是不同的值。
How to solve this problem?
如何解决这个问题呢？
回答by codingbadger
The collation will be set to case insensitive.
排序规则将设置为不区分大小写。
You need to do something like this
你需要做这样的事情
Select distinct col1 COLLATE sql_latin1_general_cp1_cs_as
From dbo.myTable
回答by radtek
Not sure about MS SQL but with MySQL or postgres, use BINARYfor this operation. Cast the column to binary like so:
不确定 MS SQL，但使用 MySQL 或 postgres，BINARY用于此操作。将列转换为二进制，如下所示：
SELECT DISTINCT BINARY(column1) from table1;
Just change column1and table1as per your schema.
只要改变column1和table1按您的架构。
Full example that works for me in MySQL 5.7, should work for others:
在 MySQL 5.7 中对我有用的完整示例，对其他人也适用：
SELECT DISTINCT BINARY(gateway) from transactions;
Cheers!
干杯!
回答by gbn
SELECT DISTINCT
CasedTheColumn
FROM
(
SELECT TheColumn COLLATE LATIN1_GENERAL_BIN AS CasedTheColumn
FROM myTAble
)FOO
WHERE
CasedTheColumn IN ('A', 'a'...)
回答by pinkgothic
Try setting the collationof the column in question to something binary, e.g. utf8-bin. You can either do that in the SELECTstatement itselfor by changing your table structure directly (which means it doesn't have to map the collation each time the query is run, since it will store it correctly internally).
尝试将相关列的排序规则设置为二进制内容，例如utf8-bin。您可以在SELECT语句本身中执行此操作，也可以直接更改表结构（这意味着不必在每次运行查询时都映射排序规则，因为它将在内部正确存储）。
