sql-server - COLLATE 子句不能用于包含 COLLATE 子句的表达式

Question

我有两个表:tab1 和 tab2。每个表只有一个 VARCHAR(MAX) 列。

我只需要获取那些值对，它们是相等的，但只有大小写不同。

示例输入:

tab1.t1     tab2.t2
-----------------------
fff     fff
FFF     fff
Fff     fff
FFF     FFA
FfA     FFF
FFF     aaa
bbb     aaa

Related output:

t1      t2
-----------------------
fff     FFF
FFF     fff
Fff     fff
Fff     FFF
FfA     FFA

表 tab1 和 tab2 在我们的真实数据库中足够大(~ 800-1000 行)。我需要为大约 500-600 列生成此操作。

所以我需要写一个快速的解决方案。我写了一个算法:

1. 使用区分大小写的排序规则从 tab1 中删除所有重复项
2. 使用区分大小写的排序规则从 tab2 中删除所有重复项
3. 使用不区分大小写的排序规则加入前面步骤的结果集
4. 使用区分大小写的排序规则过滤掉(WHERE 子句)值不相等的行
5. 使用区分大小写的排序规则删除重复行

我试过了:

SELECT DISTINCT tt.t1 COLLATE Cyrillic_General_CS_AS, tt.t2 COLLATE Cyrillic_General_CS_AS
FROM (
    SELECT tt1.t1, tt2.t2
    FROM 
    (
        SELECT tab1.t1 COLLATE Cyrillic_General_CS_AS
                       AS t1
        FROM (VALUES('fff'),('FFF'),('Fff'),('FFF'),('FfA'),('FFF'),('bbb')) AS tab1(t1)
        GROUP BY tab1.t1 COLLATE Cyrillic_General_CS_AS
    ) tt1 INNER JOIN
    (
        SELECT tab2.t2 COLLATE Cyrillic_General_CS_AS
                       AS t2
        FROM (VALUES('fff'),('fff'),('fff'),('FFA'),('FFF'),('aaa'),('aaa')) AS tab2(t2)
        GROUP BY tab2.t2 COLLATE Cyrillic_General_CS_AS
    ) tt2
    ON tt1.t1 = tt2.t2 COLLATE Cyrillic_General_CI_AS
) AS tt
WHERE tt.t1 <> tt.t2 COLLATE Cyrillic_General_CS_AS

但出现错误:

"COLLATE clause cannot be used on expressions containing a COLLATE clause."

请建议我如何在不使用用户定义函数、临时表或删除 group by 子句的情况下避免此问题(我试过了 - 它们几乎不会减慢执行速度)。

Answer 1

因为 SQL Server 可以以任何顺序处理 JOIN，并且可以

1. 在最终输出中传递要评估的表达式
2. 尽早解析表达式，早在简化数据的初始检索时就解析表达式

它不能保证强制应用 COLLATE 的阶段。因此，您只能将它应用于任何列/表达式一次。由于您在外部查询中已经有 DISTINCT，因此 GROUP BY 在派生表中是多余的 - 即使您认为它会减少中间结果集。

---

SELECT DISTINCT tt.t1 COLLATE Cyrillic_General_CS_AS, tt.t2 COLLATE Cyrillic_General_CS_AS
FROM (
    SELECT tt1.t1, tt2.t2
    FROM 
        (VALUES('fff'),('FFF'),('Fff'),('FFF'),('FfA'),('FFF'),('bbb')) tt1(t1)
    INNER JOIN
        (VALUES('fff'),('fff'),('fff'),('FFA'),('FFF'),('aaa'),('aaa')) tt2(t2)
    ON tt1.t1 = tt2.t2
) AS tt
WHERE tt.t1 <> tt.t2 COLLATE Cyrillic_General_CS_AS