为什么“SELECT DISTINCT a,b FROM…”返回的记录少于“SELECT DISTINCT a+'|'+b FROM…”?

尾随空格可能导致这种情况。对于字符串比较,这些将被忽略。

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*/

我想不出有什么情况能让你 只有更少的记录。我将通过只选择一个+“|”,然后添加更多的列来简化查询。