sql - Union 导致无法将 ntext 数据类型选为 DISTINCT，因为它不可比较

Question

我有两个要合并的查询(由一些临时表组成)。当我用联合注释掉一个(不管哪个)查询时，它就起作用了。但是当我介绍工会时，我得到 The ntext data type cannot be selected as DISTINCT because it is not comparable.我不明白为什么因为两个主要查询都没有使用不同的。以下是主要选择供引用:

SELECT  
    dbo.CUSTTABLE.ACCOUNTNUM AS 'Account Number',
    dbo.CUSTTABLE.NAME AS 'Customer Name',
    [DESCRIPTION] AS 'Division',
    ISNULL(dbo.USERINFO.NAME, 'OPEN') AS 'Sales Engineer',
    [STATE],
    PHONE,
    PAYMTERMID AS 'Terms',
    (CASE BLOCKED
        WHEN 0 THEN 'No'
        WHEN 2 THEN 'Financial Hold'
        WHEN 1 THEN 'System Hold'
        ELSE 'Unknown' END) AS 'Status',
    [45-60],
    [61-90],
    [91-120],
    [120+],
    #temptable2.Today,
    ISNULL(USERMEMO, '') AS 'Notes',
    ISNULL(#temptable2.Today / NULLIF((ISNULL(#salesTemp.PQ1, 0) / 90), 0),0) AS 'DSO1',
    CustTable.StatisticsGroup as 'Past Due Category'

FROM
    dbo.CUSTTABLE INNER JOIN 
    dbo.DIMENSIONS ON DIMENSION2_ = NUM INNER JOIN
    #temptable2 ON dbo.CUSTTABLE.ACCOUNTNUM = #temptable2.ACCOUNTNUM LEFT OUTER JOIN
    #temptable ON dbo.CUSTTABLE.ACCOUNTNUM = #temptable.ACCOUNTNUM LEFT OUTER JOIN
    dbo.SMMSALESUNITMEMBERS ON SALESGROUP = SALESUNITID AND SALESMANAGER = 1 LEFT OUTER JOIN
    dbo.USERINFO ON ID = SALESMANID AND [ENABLE] = 1    
    JOIN #salesTemp on #salesTemp.ACCOUNTNUM = CUSTTABLE.ACCOUNTNUM
WHERE
    DIMENSION2_ IN (@division)

UNION
SELECT 
    Customer_ID AS 'Account Number',
    Customer_Name AS 'Customer Name',
    'South Bend' AS Division,
    '' AS 'Sales Engineer',
    'Indiana' AS 'State',
    '' AS 'Phone',
    Customer_Terms AS 'Terms',
    '' AS 'Status',
    [45-60],
    [61-90],
    [91-120],
    [120+],
    [Today],
    '' AS 'Notes',
    '' AS 'DSO1',
    '' AS 'Past Due Category'
FROM #temptable4

Answer 1

更改:'' AS 'DSO1',到NULL as 'DS01',
SQL Server 中的空集无法转换为数字；这是基于联合的顶部选择中定义的数据类型所必需的。

我认为这是问题所在，因为联合中的顶部选择将返回一个数值。由于不能将空集转换为数字，请提供数字 (0) 或使用 null。

请记住，在执行联合时，列数必须匹配且它们的数据类型必须匹配。如果他们不这样做，你会得到这样的错误。

至于评论，“我不明白为什么因为两个主要查询都没有使用不同的”A UNION对生成的联合结果执行不同的操作。 UNION ALL将不会。这就是执行 UNION ALL 的原因执行速度更快，因为它不必执行 UNION 的执行中包含的重复删除操作.