SQL text、ntext 和 image 数据 > 类型无法比较或排序，除非使用 IS NULL 或 LIKE > 运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14979413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
提问by Junchen Liu
email belongs to table booking and its defined as type "Text" in our Microsoft sql server
电子邮件属于预订表，在我们的 Microsoft sql 服务器中定义为“文本”类型
SELECT email,
COUNT(email) AS NumOccurrences
FROM Booking
GROUP BY email
HAVING ( COUNT(email) > 1 )
after running the above query(trying to find duplicates emails in the booking) I got the error message like this:
运行上述查询（尝试在预订中查找重复的电子邮件）后，我收到如下错误消息：
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
除非使用 IS NULL 或 LIKE 运算符，否则无法比较或排序 text、ntext 和 image 数据类型。
I am using Microsoft Sql
我正在使用 Microsoft Sql
回答by John Woo
since you are using SQL Server, why not change the data type to VARCHAR(100)?
既然您正在使用SQL Server，为什么不将数据类型更改为VARCHAR(100)？
To work around this error without changing the datatype, the TEXT or NTEXTcolumn needs to be converted to VARCHAR or NVARCHARwhen used in either the ORDER BYclause or the GROUP BYclause of a SELECTstatement. eg, which is alittle bit messy
要在不更改数据类型的情况下解决此错误，TEXT or NTEXT需要将列转换为VARCHAR or NVARCHARwhen 在语句的ORDER BY子句或GROUP BY子句中使用SELECT。例如，这有点凌乱
SELECT CAST(email AS NVARCHAR(100)) email,
COUNT(CAST(email AS NVARCHAR(100))) AS NumOccurrences
FROM Booking
GROUP BY CAST(email AS NVARCHAR(100))
HAVING COUNT(CAST(email AS NVARCHAR(100))) > 1
回答by Goose
I had an issue with the accepted answer because of an issue with my ORM. This answer is only a workaround for when the accepted answer won't work, and is only valid if you only have the column in group by because it is required so you can include the column in the select.
由于我的 ORM 存在问题，我对接受的答案有疑问。此答案只是在接受的答案不起作用时的一种解决方法，并且仅当您仅在 group by 中有列时才有效，因为它是必需的，因此您可以在选择中包含该列。
It casts the column inside of aggregate function, so that it is not required in the group by, and the error will not occur and the returned data should still be correct.
它将列转换到聚合函数内部，这样在group by 中就不需要它，并且不会发生错误并且返回的数据应该仍然是正确的。
MIN(CAST(email AS NVARCHAR(4000))) as email
Ideally you'd find a way to avoid this hack, but this may be useful in some circumstances.
理想情况下，您会找到一种方法来避免这种 hack，但这在某些情况下可能很有用。
