为什么“ SELECT DISTINCT a，b FROM ...”返回的记录少于“ SELECT DISTINCT A +'|' + B FROM ...”？ [英] Why does "SELECT DISTINCT a, b FROM..." return fewer records than "SELECT DISTINCT A + '|' + B FROM..."?

问题描述

我有一个查询，该查询选择了一堆与客户名称和地址有关的字段，但是归结为：

I have a query that's selecting a bunch of fields related to names and addresses of customers but it boils down to:

SELECT DISTINCT a, b, c, ... FROM big_dumb_flat_table

它返回一堆记录（10986590 ）。当我替换选择列表中的逗号以将其格式化为管道分隔的串联字符串时：

it returns a bunch of records (10986590). When I replace the commas in the select-list to format it as a pipe-separated concatenated string:

SELECT DISTINCT a + '|' + b + '|' + c + '|' + ... FROM big_dumb_flat_table

返回248条更多记录。我向自己保证，在任何字段中都没有管道会破坏返回集合的保真度。

it's returning 248 more records. I've reassured myself that there are no pipes in any of the fields that could be screwing the fidelity of the returned set. What's going on here?

推荐答案

尾随空格可能会导致这种情况。对于字符串比较，将忽略它们。

Trailing spaces could cause this. For string comparisons these are ignored.

CREATE TABLE #T
(
a varchar(10),
b varchar(10),
c varchar(10)
)

INSERT INTO #T
SELECT 'a ' as a, 'b' as b, 'c ' as c union all
SELECT 'a' as a, 'b' as b, 'c ' as c

SELECT DISTINCT a, b, c  
FROM #T /*1 result*/

SELECT DISTINCT a + '|' + b + '|' + c + '|'   
FROM #T /*2 results*/


SELECT DISTINCT LTRIM(RTRIM(a)) + '|' + LTRIM(RTRIM(b)) + '|' +
                LTRIM(RTRIM(c)) + '|'   
FROM #T /*1 result*/

这篇关于为什么“ SELECT DISTINCT a，b FROM ...”返回的记录少于“ SELECT DISTINCT A +'|' + B FROM ...”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！