有两个字段,现在的数据是这样
id value
1 0
1 0
1 4
2 9
2 8
3 2
3 1
3 5
3 4我现在需要根据id分组,然后把对应的value字段值从新按自增写入。改完后应该是
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4请问这个如何修改
id value
1 0
1 0
1 4
2 9
2 8
3 2
3 1
3 5
3 4我现在需要根据id分组,然后把对应的value字段值从新按自增写入。改完后应该是
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4请问这个如何修改
UPDATE TB SET [VALUE]=T.[VALUE]
FROM TB
INNER JOIN (
SELECT ID,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) 'VALUE' FROM TB
) T ON TB.ID=T.ID
SELECT ID,[VALUE]=(SELECT COUNT(*) FROM #T WHERE IDD<=T.IDD) FROM #T T
SELECT ID,[VALUE]=(SELECT COUNT(*) FROM #T WHERE IDD<=T.IDD) FROM #T T
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-26 17:34:13
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[value] int)
insert [tb]
select 1,0 union all
select 1,0 union all
select 1,4 union all
select 2,9 union all
select 2,8 union all
select 3,2 union all
select 3,1 union all
select 3,5 union all
select 3,4
--------------开始查询--------------------------
SELECT id,[value]=ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [value] DESC) FROM tb
----------------结果----------------------------
/* id value
----------- --------------------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4(9 行受影响)*/
直接这么写
SELECT ID,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) 'VALUE' INTO #T FROM TB
TRUNCATE TB
INSERT INTO TB
SELECT * FROM #T
改成TRUNCATE TABLE TB
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('TEMPDB..#') IS NOT NULL DROP TABLE #
GO
CREATE TABLE TB(ID INT,[VALUE] INT)
INSERT INTO TB
SELECT 1, 0 UNION ALL
SELECT 1, 0 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 2, 9 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 5 UNION ALL
SELECT 3, 4 SELECT IDENTITY(INT,1,1) 'ID2',* INTO # FROM TBTRUNCATE TABLE TB
INSERT INTO TB
SELECT ID
,(SELECT COUNT(*) FROM # T2 WHERE T2.ID=T1.ID AND T2.ID2<=T1.ID2) 'VALUE'
FROM # T1
SELECT * FROM TB
/*
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4
*/
--这是2000的写法,2005的话用ROW_NUMBER()
--sql 2000if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[value] int)
insert [tb]
select 1,0 union all
select 1,0 union all
select 1,4 union all
select 2,9 union all
select 2,8 union all
select 3,2 union all
select 3,1 union all
select 3,5 union all
select 3,4declare @n int,@id int
set @n=0
set @id=1update tb
set @n=case when id=@id then @n+1 else 1 end,[value]=@n,@id=id
---------------------------------------
select * from tbid value
----------- -----------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4
go
create table [tb]([id] int,[value] int)
insert [tb]
select 1,0 union all
select 1,0 union all
select 1,4 union all
select 2,9 union all
select 2,8 union all
select 3,2 union all
select 3,1 union all
select 3,5 union all
select 3,4
DECLARE @VALUE INT,@NUM INT
--SET @VALUE=1
UPDATE TB SET [VALUE]=@VALUE,@VALUE=CASE WHEN ID=@NUM THEN @VALUE+1 ELSE 1 END ,@NUM=idSELECT * FROM TB
id value
----------- -----------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4(所影响的行数为 9 行)
go
create table [tb]([id] int,[value] int)
insert [tb]
select 1,0 union all
select 1,0 union all
select 1,4 union all
select 2,9 union all
select 2,8 union all
select 3,2 union all
select 3,1 union all
select 3,5 union all
select 3,4SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM TB
SELECT ID,(SELECT COUNT(*) FROM #T WHERE ID=T.ID AND IDD<=T.IDD)AS [VALUE] FROM #T T(所影响的行数为 9 行)
(所影响的行数为 9 行)ID VALUE
----------- -----------
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
3 4查询的
select top 100 percent identity(int,1,1) no,id,value into #tmp from t order by id
update a
set value=no-(select top 1 no from #tmp where id=a.id order by no)+1
from #tmp a
delete from t
insert into t (id,value) select id,value from #tmp
if object_id('tb') is not null
drop table tb
go
create table tb(id int, valued int)
insert into tb
select 1, 0 union all
select 1, 0 union all
select 1 , 4 union all
select 2 , 9 union all
select 2 , 8 union all
select 3 , 2 union all
select 3 , 1 union all
select 3, 5 union all
select 3 , 4 select id,row_number() over (partition by id order by id asc) from tb