sql server语言 - 没有SELECT DISTINCT存在，但我得到:文本数据类型不能被选择为DISTINCT，因为

Question

所以我有一个查询，没有一个SELECT DISTINCT在它，但我得到一个The text data type cannot be selected as DISTINCT because it is not comparable错误。这个相同的查询已经运行多年没有错误，突然它不工作了。我最喜欢将其中一个表中的列的数据类型更改为文本，但仍然…查询中没有DISTINCT，为什么会出现错误

下面是查询:

SELECT
            s.dept_abrev + ' ' + s.crs_num + ' ' + s.section_code AS course,
            app.approval_id,
            app.role_id,
            r.role_name,
            app.section_id,
            d.delivery_method,
            app.approve,
            app.reject,
            app.notes,
            x.xl_code,
            x.xl_cap,
            s.section_cap,
            rt.role_priority,
            p.person_id,
            p.f_name + ' ' + p.l_name AS approver_name,
            p.email,
            ip.person_id AS inst_person_id, 
            ip.f_name + ' ' + ip.l_name AS instructor_name,
            'RCDE' AS who
        FROM
            cas_Approval app
            INNER JOIN cas_Sections s ON (s.section_id = app.section_id) 
                AND s.section_status_id IN (3,4)
            INNER JOIN cas_Contract AS c ON (c.section_id = app.section_id)
            INNER JOIN cas_Instructor AS i ON (i.instructor_id = c.instructor_id)
            INNER JOIN cas_Person AS ip ON (i.person_id = ip.person_id)
            INNER JOIN cas_Role r ON (app.role_id = r.role_id)
            LEFT OUTER JOIN cas_XLCode x ON (x.xl_id = s.xl_id)
            INNER JOIN cas_DeliveryMethod d ON (d.delivery_id = s.delivery_id)
            INNER JOIN cas_RoleTypes rt ON (rt.role_type_id = r.role_type_id)
            INNER JOIN cas_PersonToRole AS ptr ON r.role_id = ptr.role_id 
            INNER JOIN cas_Person AS p ON ptr.person_id = p.person_id
            INNER JOIN cas_semesters sem ON s.semester_id = sem.semester_id
                AND sem.is_template = 0
        WHERE
            app.approve IS NULL
            AND (SELECT COUNT(reject) FROM cas_Approval WHERE section_id = s.section_id OR section_id = s.parent_section_id) = 0
            AND app.reject IS NULL 
            AND s.semester_id = 27
            AND ip.person_id <> p.person_id
        UNION
        SELECT
            c.dept_abrev + ' ' + c.crs_num + ' ' + c.sectionCode AS course,
            app.approval_id,
            app.role_id,
            r.role_name,
            app.section_id,
            (CASE c.instructMethod 
                WHEN 'P' THEN 'Traditional'
                WHEN 'I' THEN 'Online'
                WHEN 'R' THEN 'Broadcast'
            END) AS delivery_method,
            app.approve,
            app.reject,
            app.notes,
            c.xlCode AS xl_code,
            NULL AS xl_cap,
            c.courseCap AS section_cap,
            rt.role_priority,
            p.person_id,
            p.f_name + ' ' + p.l_name AS approver_name,
            p.email,
            ip.person_id AS inst_person_id,
            insFirstName + ' ' + insLastName AS instructor_name,
            'USU-E' AS who
        FROM
            cas_Approval app
            INNER JOIN cas_usuECourses c ON (c.id = app.eastId) 
                AND c.status IN ('P','A','S')
            INNER JOIN cas_Role r ON (app.role_id = r.role_id)
            INNER JOIN cas_RoleTypes rt ON (rt.role_type_id = r.role_type_id)
            INNER JOIN cas_PersonToRole AS ptr ON r.role_id = ptr.role_id 
            INNER JOIN cas_Person p ON ptr.person_id = p.person_id
            INNER JOIN cas_semesters sem ON c.termCode = sem.banner_term_code
                AND sem.is_template = 0
            LEFT OUTER JOIN cas_person ip ON c.insAnumber = ip.a_num
        WHERE
            app.approve IS NULL
            AND (SELECT COUNT(reject) FROM cas_Approval WHERE eastId = c.id) = 0
            AND app.reject IS NULL 
            AND sem.semester_id = 27
        ORDER BY
            p.person_id,
            app.role_id,
            course

Answer 1

UNION子句导致DISTINCT。

博客文章描述你的确切问题:SQL联合和文本数据类型

Answer 2

TEXT不能与DISTINCT(和其他几个函数)一起使用。在任何情况下，TEXT都是不赞成的，应该用VARCHAR(MAX)这样的类型来代替，它允许不同的。

CREATE Table SomeTable
(
  TextCol TEXT,
  VarCharMaxCol VARCHAR(MAX)
);
GO
--Err: The text data type cannot be selected as DISTINCT because it is not comparable.
SELECT DISTINCT TextCol FROM SomeTable;
-- Works
SELECT DISTINCT VarCharMaxCol FROM SomeTable;

Answer 3

文本字段是为不进行比较或排序的数据设计的。例如，图书馆永远不会把《华氏451度》放在《钟罩》旁边，因为一个开头是"It was a p…"，另一个开头是"It was a q…"。

快速解决您的问题:使用UNION ALL。长期解决方案是不使用text

SELECT CAST('' AS text) UNION SELECT CAST('' AS text) <——error

SELECT CAST('' AS text) UNION ALL SELECT CAST('' AS text) <——无错误