原本有两列数据F1,F2,
我想把表中相同F1下f2列中重复的值改成唯一值,打算按照F1对应最大F2值来递增,
比如某个F1对应最大F2是100,那么接下去就是把重复值更新为101,102,
我增加了一个自增列ID1,写了下面的SQL,可是执行完后发现只有第一个重复数据的F2的值是对的,后面其他值并没有随着递增,请问这是为什么?UPDATE dbo.Table_1 SET F2=(SELECT MAX(F2)+1 FROM dbo.Table_1 WHERE F1=B1.F1)
FROM dbo.Table_1,
(
SELECT MAX(ID1) AS ID,dbo.Table_1.F1,dbo.Table_1.F2 FROM
dbo.Table_1,
(
SELECT F1,F2 FROM dbo.Table_1 GROUP BY F1,F2 HAVING(COUNT(1)>1)) A1
WHERE dbo.Table_1.F1=A1.F1 AND dbo.Table_1.F2=A1.F2 GROUP BY dbo.Table_1.F1,dbo.Table_1.F2
) B1 WHERE dbo.Table_1.ID1=B1.ID AND dbo.Table_1.F1=B1.F1
我想把表中相同F1下f2列中重复的值改成唯一值,打算按照F1对应最大F2值来递增,
比如某个F1对应最大F2是100,那么接下去就是把重复值更新为101,102,
我增加了一个自增列ID1,写了下面的SQL,可是执行完后发现只有第一个重复数据的F2的值是对的,后面其他值并没有随着递增,请问这是为什么?UPDATE dbo.Table_1 SET F2=(SELECT MAX(F2)+1 FROM dbo.Table_1 WHERE F1=B1.F1)
FROM dbo.Table_1,
(
SELECT MAX(ID1) AS ID,dbo.Table_1.F1,dbo.Table_1.F2 FROM
dbo.Table_1,
(
SELECT F1,F2 FROM dbo.Table_1 GROUP BY F1,F2 HAVING(COUNT(1)>1)) A1
WHERE dbo.Table_1.F1=A1.F1 AND dbo.Table_1.F2=A1.F2 GROUP BY dbo.Table_1.F1,dbo.Table_1.F2
) B1 WHERE dbo.Table_1.ID1=B1.ID AND dbo.Table_1.F1=B1.F1
update
a
set
f2=b.id
from
Table_1 a,
(select id=row_number()over(partition by f1 order by getdate()),* from Table_1)b
where
a.f1=b.f1
来点数据和列出要的结果!!
drop table #t1
CREATE TABLE #t1(f1 varchar(30) ,f2 int)
insert into #t1(f1,f2)
select 'Aec',500 union all
select 'Aec',500 union all
select 'Aec',900 union all
select 'Aec',900 union all
select 'Aec',1000
CREATE TABLE #t1(f1 varchar(30) ,f2 int)
insert into #t1(f1,f2)
select 'Aec',500 union all
select 'Aec',500 union all
select 'Aec',900 union all
select 'Aec',900 union all
select 'Aec',1000
go;with AcHerat as
(
select *,rid=row_number() over (partition by f1 order by getdate()),
upf2 = (select max(f2) from #t1 where f1 = t.f1)
from #t1 t
)update a
set a.f2 = b.upf2+b.rid
from #t1 a,AcHerat b
where a.f1 = b.f1 and a.f2 = b.f2select *
from #t1drop table #t1
/*****************f1 f2
------------------------------ -----------
Aec 1002
Aec 1001
Aec 1003
Aec 1004
Aec 1005(5 行受影响)
USE SSISTest;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
F1 nvarchar(10),
F2 int)
INSERT INTO A--插入测试数据
select 'Aec',500 union all
select 'Aec',500 union all
select 'Aec',900 union all
select 'Aec',1000 union all
select 'Aec1',5000 union all
select 'Aec1',5000 union all
select 'Aec1',9000 union all
select 'Aec1',10000 union all
select 'Aec1',9000 union allselect 'Aec',900
go
;with cte as
(
select *,row_number()over(partition by F1 order by getdate()) as RN from a
),
cte1 as
(
select *,row_number()over(partition by F1 order by F2 desc) as RN from a
)
,cte2 as
(
select F1,F2,NewF2=(select F2 from cte1 as b where a.F1=b.F1 and b.RN=1)+RN,RN,
row_number()over(partition by F1 order by F2 desc) as RNN from cte as a
)
select F1,F2,case when RNN=1 then F2 else NewF2 end NewF2 from cte2 order by F1,RN
/*
F1 F2 NewF2
---------- ----------- --------------------
Aec 500 1001
Aec 500 1002
Aec 900 1003
Aec 1000 1000
Aec 900 1005Aec1 5000 10001
Aec1 5000 10002
Aec1 9000 10003
Aec1 10000 10000
Aec1 9000 10005
*/
USE SSISTest;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
F1 nvarchar(10),
F2 int)
INSERT INTO A--插入测试数据
select 'Aec',500 union all
select 'Aec',500 union all
select 'Aec',900 union all
select 'Aec',1000 union all
select 'Aec1',5000 union all
select 'Aec1',5000 union all
select 'Aec1',9000 union all
select 'Aec1',10000 union all
select 'Aec1',9000 union allselect 'Aec',900
go
;with cte as
(
select *,row_number()over(partition by F1 order by getdate()) as RN from a
),
cte1 as
(
select *,row_number()over(partition by F1 order by F2 desc) as RN from a
)select F1,F2,NewF2=(select F2 from cte1 as b where a.F1=b.F1 and b.RN=1)+RN
from cte as a order by F1,RN/*
F1 F2 NewF2
---------- ----------- --------------------
Aec 500 1001
Aec 500 1002
Aec 900 1003
Aec 1000 1004
Aec 900 1005Aec1 5000 10001
Aec1 5000 10002
Aec1 9000 10003
Aec1 10000 10004
Aec1 9000 10005*/
CREATE TABLE #t1(f1 varchar(30) ,f2 int)
insert into #t1(f1,f2)
select 'A',100 union all
select 'A',120 union all
select 'A',160 union all
select 'A',90 union all
select 'A',75 union all
select 'B',130 union all
select 'B',140 union all
select 'B',180 union all
select 'B',120 union all
select 'B',100 union all
select 'C',300 union all
select 'C',200 union all
select 'C',310 union all
select 'C',360 union all
select 'C',120;with TempA as (select Row_Number()over(partition by f1 order by f2 desc) as num ,* from #t1)
, TempB as(select max(f2)over(partition by f1)+num as f3,f1,f2 from TempA )
update #t1 set f2=f3 from TempB as b where #t1.f1=b.f1 and #t1.f2=b.f2select * from #t1f1 f2
------------------------------ -----------
A 163
A 162
A 161
A 164
A 165
B 183
B 182
B 181
B 184
B 185
C 363
C 364
C 362
C 361
C 365(15 row(s) affected)
有些理解错了,这是原数据
f1 f2
-------------------
Aec 500
Aec 500
Aec 900
Aec 900
Aec 1000这是目标数据
f1 f2
--------------------------
Aec 500
Aec 1001
Aec 900
Aec 1002
Aec 1000
select 'Aec', 500 union all
select 'Aec', 500 union all
select 'Aec', 900 union all
select 'Aec', 900 union all
select 'Aec', 1000declare @a int,@b int,@n int
select @n=max(f2) from #tempupdate #temp set @a=@b,@b=f2,
@n=case when @a=f2 then @n+1 else @n end,
f2=case when @a=f2 then @n else f2 endselect * from #temp
drop table #temp
/*
f1 f2
---- -----------
Aec 500
Aec 1001
Aec 900
Aec 1002
Aec 1000
*/
create table #tb(f1 nvarchar(10),f2 int)
insert #tb
select 'aa' ,120 union all
select 'aa' ,180 union all
select 'aa' ,120 union all
select 'aa' ,160 union all
select 'aa' ,160 union allselect 'bb' ,255 union all
select 'bb' ,360 union all
select 'bb' ,360 union all
select 'bb' ,255 union all
select 'bb' ,255 union allselect 'cc' ,70 union all
select 'cc' ,80 union all
select 'cc' ,70 union all
select 'cc' ,160 union all
select 'cc' ,150 ;with TempA as (select row_number()over(partition by f1 order by f2) as num,* from #tb)
,TempB as (select row_number()over(partition by a.f1 order by a.f2) as num,a.f1,a.f2 from TempA as a left join TempA as b
on a.f1=b.f1 and a.f2=b.f2 and a.num+1=b.num where b.num is not null)
update TempB set f2=(select max(t.f2) from #tb as t where t.f1=TempB.f1)+numselect * from #tb
create table #temp(f1 varchar(3),f2 int)insert #temp
select 'Aec', 500 union all
select 'Aec', 500 union all
select 'Aec', 900 union all
select 'Aec', 900 union all
select 'Aec', 1000
with t as (
select ROW_NUMBER()over(order by getdate()) id ,*,MAX(f2)over(partition by f1) maxVal
,ROW_NUMBER () over(partition by f1,f2 order by f2) sn from #temp)
,t1 as (select id,f1,f2 from t where sn=1 union all
select id, f1, maxVal +ROW_NUMBER ()over (order by sn) from t where sn<>1)
select f1,f2 from t1 order by id
f1 f2
Aec 500
Aec 1001
Aec 900
Aec 1002
Aec 1000