sql-server - 如何修复 "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Question

这是我到目前为止:

SELECT 
    account, ' ', message_type, 
    MAX(message_no) as max_message_no, message_text
FROM
    (SELECT 
         account, message_type, message_no, message_text
     FROM  
         messages m 
     INNER JOIN 
         receivables r ON m.account = r.aracct
     WHERE 
         m.account IN (SELECT r.aracct AS account 
                       FROM receivables r 
                       WHERE r.balance <> 0)
         AND m.message_type = 'N') t1
GROUP BY 
    t1.account, t1.message_type, t1.message_text

我不断收到错误消息:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

我基本上想要最大值 message_no来自每个帐户，我希望它显示 message_text ，但它不会允许它，因为它是 text数据类型。

Answer 1

TEXT , NTEXT和 IMAGE已弃用，应替换为相应的类型 VARCHAR(MAX) , NVARCHAR(MAX)和 VARBINARY(MAX) .你可能会投你的 TEXT -列与

CAST(t1.message_text AS VARCHAR(MAX))

在您的子选择中执行一次可能就足够了:

SELECT account, ' ', message_type, MAX(message_no) as max_message_no, message_text
FROM
(
SELECT account, message_type, message_no, CAST(message_text AS VARCHAR(MAX)) AS message_text
    FROM messages m INNER JOIN receivables r ON m.account = r.aracct
        WHERE m.account IN (SELECT r.aracct AS account FROM receivables r WHERE r.balance <> 0)
        AND m.message_type = 'N'
) t1
GROUP BY t1.account, t1.message_type, t1.message_text