34,594
社区成员
发帖
与我相关
我的任务
分享
declare @dghousepic table (newcode int, image varbinary(10))
insert @dghousepic select 1, 0x1
insert @dghousepic select 1, 0x2
insert @dghousepic select 1, 0x3
insert @dghousepic select 2, 0xa
insert @dghousepic select 2, 0xb
insert @dghousepic select 2, 0xc
--> 都说了某列是指在单个newcode内是唯一的:
select * from @dghousepic a where image=(select top 1 image from @dghousepic where newcode=a.newcode)
/*
newcode image
----------- ---------
1 0x01
2 0x0A
*/
--> 加个重复的
insert @dghousepic select 1, 0x1 -->这张照片重复,而且 top 1 刚好命中
select * from @dghousepic a where image=(select top 1 image from @dghousepic where newcode=a.newcode)
/*
newcode image
----------- ---------
1 0x01
2 0x0A
1 0x01
*/
--> 生成唯一的识别码处理:2000
select id=identity(int,1,1),* into #dghousepic from @dghousepic
select newcode,image from #dghousepic a where id=(select top 1 id from #dghousepic where newcode=a.newcode)
/*
newcode image
----------- ---------
1 0x01
2 0x0A
*/
--> 生成唯一的识别码处理:2005
;with cte (id,newcode,image) as
(
select row_number() over (order by newcode),* from @dghousepic
)
select newcode,image from cte a where id=(select top 1 id from cte where newcode=a.newcode)
/*
newcode image
----------- ---------
1 0x01
2 0x0A
*/