有没有办法在 SQL Server 2005 中按文本（或 ntext）进行 DISTINCT 或分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1028061/
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
Is there any way to DISTINCT or group by a text (or ntext) in SQL Server 2005?
提问by Ascalonian
In a table, I have a column called MEMO_TEXT that is a text data type. When I try creating a view and use a GROUP BY, I get the following error:
在一个表中，我有一个名为 MEMO_TEXT 的列，它是一种文本数据类型。当我尝试创建视图并使用 GROUP BY 时，出现以下错误：
SQL Server Database Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
SQL Server 数据库错误：无法比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。
I get this error when I try to do a DISTINCT on the query as well. Any ideas on how to get around this?
当我尝试对查询执行 DISTINCT 时，我也会收到此错误。关于如何解决这个问题的任何想法？
If you need any more information, please let me know.
如果您需要更多信息，请告诉我。
回答by Rowland Shaw
One hack around it is to cast it as an nvarchar(max).
围绕它的一个技巧是将其转换为nvarchar(max).
This is a documentedway to increase the string length beyond 4,000:
这是将字符串长度增加到 4,000 以上的记录方法：
nvarchar [ ( n | max ) ]Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.
nvarchar [ ( n | max ) ]可变长度的 Unicode 字符串数据。n 定义字符串长度，可以是 1 到 4,000 之间的值。max 表示最大存储大小为 2^31-1 字节（2 GB）。存储大小（以字节为单位）是输入数据实际长度的两倍 + 2 个字节。nvarchar 的 ISO 同义词是不同的国家字符和不同的国家字符。
A similar trick applies to varchar().
类似的技巧适用于varchar()。
回答by Scott Ivey
Try these...
试试这些...
SELECT DistinctMemo = DISTINCT(CAST(MEMO_TEXT AS varchar(max)))
FROM MyTable
-- or
SELECT DistinctMemo = CAST(MEMO_TEXT AS varchar(max))
FROM MyTable
GROUP BY CAST(MEMO_TEXT AS varchar(max))
回答by HLGEM
Do you know that there will never be repeated data in the ntext field? You could do the distinct in a derived table on the other fields and then join to the table with the ntext field and grab it in the outer query.
你知道ntext字段永远不会有重复的数据吗？您可以在其他字段的派生表中执行不同的操作，然后使用 ntext 字段连接到表并在外部查询中获取它。
something like (assume field3 is the ntext field)
类似于（假设 field3 是 ntext 字段）
select mt.idfield, a.field1, a.field2, mt.field3
from mytable mt
join
(select disitinct mt1.idfield, mt1.field1, mot.field2 from mytable mt1
join myothertable mot on mt1.idfield = mot.idfield) a
on a.ifield = mt.idfield
回答by Robert Harvey
Can you just change the data type of the column to nvarchar(max)?
您可以将列的数据类型更改为 nvarchar(max) 吗？
Consider putting in another column that takes the first 40 characters or so of your memo field, and group on that. Grouping on a memo field is going to be really slow if you have a lot of text in there.
考虑放入另一列，其中包含备注字段的前 40 个字符左右，并对其进行分组。如果你有很多文本，在备忘录字段上分组会很慢。
UPDATE myTable SET myNewField = LEFT(myOldField, 40);
