连接2个表，区分大小写 [英] join 2 tables case sensitive upper and lower case
问题描述
我有2个表，需要获取品牌代码的结果。
I have 2 tables and need to get result on brand code.
在数据库中，例如，我有2个不同的品牌，但是它们的代码是相同的（只是区别）大写和小写）。例如：
In the database i have for example 2 different brands but their code is the same (only difference in lower and upper case). For example:
代号
ab Nike
AB阿迪达斯
code Name ab Nike AB Adidas
内部如何联接代码上的2个表以分别获得这2个表？
How to inner join 2 tables on code to get this 2 separately?
现在在内部联接之后，我得到这2的总和。
Right now after inner join i get total sum of this 2.
SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON A.Code=B.Code
GROUP BY Code, BrandName
此查询会给我错误的结果，因为它不区分大小写。
This query will give me wrong result as it does not join sensitively on upper and lower case.
请帮助:)
推荐答案
至少有两个快捷方式
1。。您可以指定区分大小写的排序规则（用于比较字符集中字符间字符串的规则） A.Code和B.Code。在MySQL和其他一些数据库管理系统中，默认排序规则不区分大小写。
1. You specify a case-sensitive collation (rules for comparing strings across characters in a character set) for A.Code and B.Code. In MySQL and a few other database management systems, the default collation is case-insensitive.
也就是说，假设您使用的是MySQL或类似版本，则必须这样修改语句：
That is, assuming that you're using MySQL or similar, you'll have to modify your statement as such:
SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON A.Code=B.Code COLLATE latin1_bin
GROUP BY Code, BrandName
但是，如果您打算仅对A和B执行区分大小写的查询，则可能需要将这两个表的默认排序规则设置为区分大小写。
If, however, you plan on only performing case-sensitive queries on A and B, it may be in your interest to set the default collation on those two tables to case-sensitive.
2。。将A.Code和B.Code转换为二进制字符串，然后将两者进行比较。这是一种按字节比较两个字符串的简单方法，从而实现了不区分大小写。
2. Cast A.Code and B.Code to a binary string and compare the two. This is an simple way to compare two strings, byte-by-byte, thus achieving case-insensitivity.
SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON BINARY A.Code=B.Code
GROUP BY Code, BrandName
这篇关于连接2个表，区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
