图像字段的明显错误 [英] Distinct error with image field

问题描述

我有一个包含 Image 列的表格.现在我想选择图像列的不同值，但它给出了错误.列名也是 Image.

I have table which contains a Image column. Now I want to select distinct value of image column, but it gives error. Column name is also Image.

我的查询是:从 tbl1 中选择图像

My query is: select Image from tbl1

错误是:无法将图像数据类型选为 DISTINCT，因为它不具有可比性.

Error is: The image data type cannot be selected as DISTINCT because it is not comparable.

那么如何从 tb1 表中选择不同的值

So how to select distinct value from tb1 table

推荐答案

包含 DISTINCT 子句的 SELECT 语句中的图像数据类型.根据所使用的 SQL Server 版本，有几种方法可以克服此限制.

Image data types in a SELECT statement that contains the DISTINCT clause. Depending on the version of SQL Server being used, there are a few ways of overcoming this restriction.

对于 SQL Server 2000，TEXT 列可以转换为 VARCHAR 数据类型，NTEXT 列可以转换为 NVARCHAR 数据类型，而 IMAGE 数据类型可以转换为 VARBINARY 数据类型.前面包含 DISTINCT 子句的 SELECT 语句可以改写如下并避免错误消息:

For SQL Server 2000, a TEXT column can be converted to a VARCHAR data type, an NTEXT column can be converted to an NVARCHAR data type while an IMAGE data type can be converted to VARBINARY data type. The SELECT statements earlier which contain the DISTINCT clause can be rewritten as follows and avoid the error message:

SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(4000)) AS [BookSummary]从 [dbo].[书]

SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(4000)) AS [BookSummary] FROM [dbo].[Book]

SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(8000)) AS [BookImage]从 [dbo].[书]对于 SQL Server 2005 和 SQL Server 2008(及更高版本)，不是将 NVARCHAR 限制为 4000 个字符或将 VARCHAR 或 VARBINARY 限制为 8000 个字符，而是可以在其位置使用 MAX 说明符，如以下 SELECT 语句所示:

SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(8000)) AS [BookImage] FROM [dbo].[Book] For SQL Server 2005 and SQL Server 2008 (and later), instead of limiting the NVARCHAR to 4000 characters or the VARCHAR or VARBINARY to 8000 characters, the MAX specifier can be used in its place, as can be seen in the following SELECT statements:

SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(MAX)) AS [BookSummary]来自 [dbo].[书籍]

SELECT DISTINCT [BookTitle], CAST([BookSummary] AS NVARCHAR(MAX)) AS [BookSummary] FROM [dbo].[Book]

SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(MAX)) AS [BookImage]从 [dbo].[书]如果使用 SQL Server 2005 或 SQL Server 2008(或更高版本)，另一种克服此限制和此错误消息的方法，而不使用 CAST 或 CONVERT 函数将 text、ntext 和 image 数据类型分别转换为 varchar、nvarchar 和 varbinary，是将列的数据类型更改为 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX).Microsoft SQL Server 的未来版本中将删除 TEXT、NTEXT 和 IMAGE 数据类型，应避免使用这些数据类型.

SELECT DISTINCT [BookTitle], CAST([BookImage] AS VARBINARY(MAX)) AS [BookImage] FROM [dbo].[Book] If using SQL Server 2005 or SQL Server 2008 (or later), another way of overcoming this restriction and this error message without using the CAST or CONVERT function to convert the text, ntext and image data types to varchar, nvarchar and varbinary, respectively, is to change the data types of the columns to VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX). TEXT, NTEXT and IMAGE data types will be removed in a future version of Microsoft SQL Server and use of these data types should be avoided.

参考:http://www.sql-server-helper.com/error-messages/msg-421.aspx

这篇关于图像字段的明显错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！