select distinct .....,CAST(PHOTO AS VARBINARY) as photo into #Tmp from A drop table A select * into A from #Tmp drop table #Tmp
select distinct cast(PHOTO as VARBINARY(max))PHOTO into #Tmp from dbo.Blueprint--需要轉換后處理
select distinct id,image,name,CAST(image AS VARBINARY) as photo into #Tmp from image drop table image select * into image from #Tmp drop table #Tmp 运行了之后 服务器: 消息 8163,级别 16,状态 3,行 1 不能以 DISTINCT 方式选择 text、ntext 或 image 数据类型。三楼的方法好像还是解决不了image类型的问题 我的实际的表名image字段有id,name,image
create table tb(id int,info ntext) insert into tb select 1,'akjhiasdf' insert into tb select 2,'nonasf9sdafawe' insert into tb select 2,'nonasf9sdafawe' go alter table tb add s_id int identity(1,1) go delete from tb where s_id in( select a.s_id from tb a inner join tb b on b.s_id<a.s_id and b.id=a.id and convert(nvarchar(max),b.info)=convert(nvarchar(max),a.info)) go alter table tb drop column s_id go select * from tb go drop table tb /* id info ----------- ---------------------------------------- 1 akjhiasdf 2 nonasf9sdafawe */
select distinct .....,CAST(PHOTO AS VARBINARY) as photo into #Tmp from A drop table A select * into A from #Tmp drop table #Tmp
drop table A
select * into A from #Tmp
drop table #Tmp
drop table image
select * into image from #Tmp
drop table #Tmp
运行了之后
服务器: 消息 8163,级别 16,状态 3,行 1
不能以 DISTINCT 方式选择 text、ntext 或 image 数据类型。三楼的方法好像还是解决不了image类型的问题
我的实际的表名image字段有id,name,image
insert into tb select 1,'akjhiasdf'
insert into tb select 2,'nonasf9sdafawe'
insert into tb select 2,'nonasf9sdafawe'
go
alter table tb add s_id int identity(1,1)
go
delete from tb where s_id in(
select a.s_id from tb a inner join tb b on b.s_id<a.s_id and b.id=a.id and convert(nvarchar(max),b.info)=convert(nvarchar(max),a.info))
go
alter table tb drop column s_id
go
select * from tb
go
drop table tb
/*
id info
----------- ----------------------------------------
1 akjhiasdf
2 nonasf9sdafawe
*/
drop table A
select * into A from #Tmp
drop table #Tmp
我是自己手动把PHOTO改成binary
然后就可以使用distinct 关键字进行删除操作了……
谢谢大家了……