SQL 更新语句，立即解决立即给分

Question

LINE ITEMID ID 
    001    1 
    001    2 
    001    3 
    002    4 
    002    5 
    002    6 更新之后： 
LINE ITEMID ID 
1    001    1 
2    001    2 
3    001    3 
1    002    4 
2    002    5 
3    002    6 
..  ..      .. 
怎么写这一个更新语句？

Answer 1

update a set
    line = (select count(*) from tb
            where itemid=a.itemid and id >= a.id)
from tb as a

Answer 2

update t
set line=(select count(1)+1 from tb where itemid=t.itemid and id<t.id)
from tb t

Answer 3

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([LINE] sql_variant,[ITEMID] varchar(3),[ID] int)
insert [tb]
select null,'001',1 union all
select null,'001',2 union all
select null,'001',3 union all
select null,'002',4 union all
select null,'002',5 union all
select null,'002',6
 update t
set line=(select count(1)+1 from tb where itemid=t.itemid and id<t.id)
from tb tselect * from tb/**
1 001 1
2 001 2
3 001 3
1 002 4
2 002 5
3 002 6
**/

Answer 4

-- =============================================
-- Author:      T.O.P
-- Create date: 2009/11/25
-- Version:     SQL SERVER 2005
-- =============================================
declare @TB table([LINE] int,[ITEMID] varchar(3),[ID] int)
insert @TB
select 0,'001',1 union all
select 0,'001',2 union all
select 0,'001',3 union all
select 0,'002',4 union all
select 0,'002',5 union all
select 0,'002',6update t
set line = (select count(1) from @tb where t.[ITEMID]=[ITEMID] and t.[ID]>=[ID])
from @tb tselect * from @TB 
--测试结果:
/*
LINE        ITEMID ID
----------- ------ -----------
1           001    1
2           001    2
3           001    3
1           002    4
2           002    5
3           002    6(6 row(s) affected)
*/

Answer 5

--2005
update
 tb
set
 line=(select row_number()over(prtition by ITEMID order by getdate()) from tb)

Answer 6

--> Title  : Generating test data [tb]
--> Author : 
--> Date   : 2009-11-25 11:20:28
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ITEMID nvarchar(6),ID int,LINE sql_variant)
insert into [tb]
select '001',1,null union all
select '001',2,null union all
select '001',3,null union all
select '002',4,null union all
select '002',5,null union all
select '002',6,null
declare @ITEMID nvarchar(6),@LINE int
update tb set 
    @LINE= case when @ITEMID=ITEMID then @LINE+1 else 1 end,
    @ITEMID=ITEMID,LINE=@LINE
select * from tb
/*
ITEMID ID          LINE
------ ----------- ---------------
001    2           2
001    3           3
002    4           1
002    5           2
002    6           3(6 個資料列受到影響)
*/

Answer 7

;with cte as
(
select *, row_number() over(partition by ITEMID order by ID) as seq
from tb
)
update cte set LINE=seq

Answer 8

declare @tb table( line int,itemod nvarchar(10),id int)
insert into @tb(itemod,id) select '001',1
               union all   select '001',2
               union all   select '001',3
               union all   select '002',4
               union all   select '002',5
               union all   select '002',6
--select *,序号=(select count(1)from @tb 
--where a.itemod=itemod and id<=a.id) from @tb a
update a  set line=b.序号
from @tb a join 
(select *,序号=(select count(1)from @tb 
where a.itemod=itemod and id<=a.id) from @tb a) b
on a.id=b.id
select * from @tb
/*line        itemod     id
----------- ---------- -----------
1           001        1
2           001        2
3           001        3
1           002        4
2           002        5
3           002        6(6 行受影响)
*/

Answer 9

/*
ITEMID ID          LINE
------ ----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
001    1           1
001    2           2
001    3           3
002    4           1
002    5           2
002    6           3(6 個資料列受到影響)
*/

Answer 10

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-25 11:27:41
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([LINE] sql_variant,[ITEMID] varchar(3),[ID] int)
insert [tb]
select null,'001',1 union all
select null,'001',2 union all
select null,'001',3 union all
select null,'002',4 union all
select null,'002',5 union all
select null,'002',6
--------------开始查询--------------------------
;with f as
(
select *, row_number() over(partition by ITEMID order by ID) as line1 from tb
)
update f set LINE=line1
select * from tb
----------------结果----------------------------
/* 
(6 行受影响)(6 行受影响)
LINE                                                                                                                                                                                                                                                             ITEMID ID
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ -----------
1                                                                                                                                                                                                                                                                001    1
2                                                                                                                                                                                                                                                                001    2
3                                                                                                                                                                                                                                                                001    3
1                                                                                                                                                                                                                                                                002    4
2                                                                                                                                                                                                                                                                002    5
3                                                                                                                                                                                                                                                                002    6(6 行受影响)
*/