SQL 更新表的语句 急，麻烦大侠救命！

Question

先有表如下：
ID/PR/Material/Qty已经有信息了，现在需要更新Serial的栏位信息，原先Serial全是null值。
更新的规则如下：
PR相同，Serial的值等于 ID + PR + (三位的流水号，从001-999)，如下表所示
ID PR Material QTY Serial
001 2 A         2 001-2-001
001 2 B         7 001-2-002
001 2 A         10 001-2-003
001 2 C         2 001-2-004
001 2 D         1 001-2-005
001 2 A         1 001-2-006
001 2 B         1 001-2-007
001 2 E         19 001-2-008
001 2 F         10 001-2-009
002 3 A         12 002-3-001急，麻烦大侠救命

Answer 1

本帖最后由 libin_ftsafe 于 2008-04-11 11:12:48 编辑

  

Answer 2

select * , px = identity(int,1,1) into tmp from tb--search
select ID,PR,Material,QTY,Serial = ID+'-'+PR+'-'+right('000'+cast((select count(1) from tmp where pr = t.pr and px < t.px) + 1 as varchar),3) from tmp t

Answer 3

create table t(ID varchar(4),PR varchar(4),Material varchar(4),QTY int,Serial varchar(10))
insert into t values('001','2','A',2 ,null)
insert into t values('001','2','B',7 ,null)
insert into t values('001','2','A',10,null) 
insert into t values('001','2','C',2 ,null)
insert into t values('001','2','D',1 ,null)
insert into t values('001','2','A',1 ,null)
insert into t values('001','2','B',1 ,null)
insert into t values('001','2','E',19,null) 
insert into t values('001','2','F',10,null) 
insert into t values('002','3','A',12,null) 
goalter table t add rowid int identity(1,1)
goupdate a
set
    a.Serial=ID+'-'+PR+'-'+right('00'+rtrim((select count(1) from t where a.ID=ID and a.ROWID>=ROWID)),3)
from
    t a
goalter table t drop column rowid
goselect * from t
/*
ID   PR   Material QTY         Serial     
---- ---- -------- ----------- ---------- 
001  2    A        2           001-2-001
001  2    B        7           001-2-002
001  2    A        10          001-2-003
001  2    C        2           001-2-004
001  2    D        1           001-2-005
001  2    A        1           001-2-006
001  2    B        1           001-2-007
001  2    E        19          001-2-008
001  2    F        10          001-2-009
002  3    A        12          002-3-001
*/
godrop table t
go

Answer 4

create table os(id varchar(10),pr int,material varchar(10),qty int,serial varchar(20))
insert into os select '001',2,'A',2,null
insert into os select '001',2,'B',7,null
insert into os select '001',2,'A',10,null
insert into os select '001',2,'C',2,null
insert into os select '001',2,'D',1,null
insert into os select '001',2,'A',1,null
insert into os select '001',2,'B',1,null
insert into os select '001',2,'E',19,null
insert into os select '001',2,'F',10,null
insert into os select '002',3,'A',12,nullselect px=identity(int,1,1),* into # from osupdate os set serial=a.id+'-'+ltrim(a.pr)+'-'+
(select right('000'+ltrim(count(1)),3) from # where id=a.id and px<=a.px) 
from # a,os b
where a.id=b.id and a.pr=b.pr and a.material=b.material and a.qty=b.qtyselect * from os

Answer 5

在sql server 2000中,考虑ID PR Material QTY 可能出现重复,需要使用我上面临时表的方法来做.
在sql server 2005中,可使用row_number,不需要临时表.

Answer 6

有关2005中row_number的方法就不写了,参考如下:ROW_NUMBER()说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
语法：ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 
      <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。示例：
/*以下示例将根据年初至今的销售额，返回 AdventureWorks 中销售人员的 ROW_NUMBER。*/USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
/*
FirstName  LastName    Row Number  SalesYTD      PostalCode
---------  ----------  ----------  ------------  ----------------------------
Shelley    Dyck        1           5200475.2313  98027
Gail       Erickson    2           5015682.3752  98055
Maciej     Dusza       3           4557045.0459  98027
Linda      Ecoffey     4           3857163.6332  98027
Mark       Erickson    5           3827950.238   98055
Terry      Eminhizer   6           3587378.4257  98055
Michael    Emanuel     7           3189356.2465  98055
Jauna      Elson       8           3018725.4858  98055
Carol      Elliott     9           2811012.7151  98027
Janeth     Esteves     10          2241204.0424  98055
Martha     Espinoza    11          1931620.1835  98055
Carla      Eldridge    12          1764938.9859  98027
Twanna     Evans       13          1758385.926   98055
(13 行受影响)
*/
 
/*以下示例将返回行号为 50 到 60（含）的行，并以 OrderDate 排序。*/ 
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader ) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber between 50 and 60;
/*
SalesOrderID OrderDate               RowNumber
------------ ----------------------- --------------------
43708        2001-07-03 00:00:00.000 50
43709        2001-07-03 00:00:00.000 51
43710        2001-07-03 00:00:00.000 52
43711        2001-07-04 00:00:00.000 53
43712        2001-07-04 00:00:00.000 54
43713        2001-07-05 00:00:00.000 55
43714        2001-07-05 00:00:00.000 56
43715        2001-07-05 00:00:00.000 57
43716        2001-07-05 00:00:00.000 58
43717        2001-07-05 00:00:00.000 59
43718        2001-07-06 00:00:00.000 60
(11 行受影响)
*/

Answer 7

未必需要临时表。
CREATE TABLE tb(ID VARCHAR(10), PR INT, Material VARCHAR, QTY INT , Serial VARCHAR(20))
GO
INSERT tb(ID ,PR,Material,QTY) SELECT '001', 2, 'A'  ,2
UNION ALL SELECT '001', 2, 'B'         ,7
UNION ALL SELECT '001', 2, 'A'        ,1
UNION ALL SELECT '001', 2, 'C'        ,2
UNION ALL SELECT '001', 2, 'D'        ,2
UNION ALL SELECT '001', 2, 'A'         ,1
UNION ALL SELECT '001', 2, 'B'         ,11
UNION ALL SELECT '001', 2, 'E'       ,13
UNION ALL SELECT '001', 2, 'F'          ,21
UNION ALL SELECT '002', 3, 'A'        ,2
GO
SELECT * FROM tb
/*001 2 A 2 NULL
001 2 B 7 NULL
001 2 A 1 NULL
001 2 C 2 NULL
001 2 D 2 NULL
001 2 A 1 NULL
001 2 B 11 NULL
001 2 E 13 NULL
001 2 F 21 NULL
002 3 A 2 NULL
*/
DECLARE @i INT,@t VARCHAR(10)
SELECT @i=0,@t=''UPDATE tb SET @i=CASE WHEN @t=ID THEN @i+1 ELSE 1 END,@t=ID,Serial=@t+'-'+RTRIM(pr)+'-'+RIGHT('000'+RTRIM(@i),3)
SELECT * FROM tb
/*
001 2 A 2 001-2-001
001 2 B 7 001-2-002
001 2 A 1 001-2-003
001 2 C 2 001-2-004
001 2 D 2 001-2-005
001 2 A 1 001-2-006
001 2 B 11 001-2-007
001 2 E 13 001-2-008
001 2 F 21 001-2-009
002 3 A 2 002-3-001*/
GODROP TABLE tb
GO

Answer 8

刚上csdn时见过大力，后来就没见过了。

Answer 9

create table t(ID varchar(4),PR varchar(4),Material varchar(4),QTY int,Serial varchar(10))
insert into t values('001','2','A',2 ,null)
insert into t values('001','2','B',7 ,null)
insert into t values('001','2','A',10,null) 
insert into t values('001','2','C',2 ,null)
insert into t values('001','2','D',1 ,null)
insert into t values('001','2','A',1 ,null)
insert into t values('001','2','B',1 ,null)
insert into t values('001','2','E',19,null) 
insert into t values('001','2','F',10,null) 
insert into t values('002','3','A',12,null) 
godeclare @i int
declare @ID varchar(4),@PR varchar(4)
set @I = 1update t
set serial = ID+'-'+PR+'-'+right('000'+rtrim(@I),3),@i = case when id = @id and pr = @pr then @i + 1 else 1  end
,@id = id,@pr = pr
select * from tdrop table t/*
ID   PR   Material QTY         Serial     
---- ---- -------- ----------- ---------- 
001  2    A        2           001-2-001
001  2    B        7           001-2-002
001  2    A        10          001-2-003
001  2    C        2           001-2-004
001  2    D        1           001-2-005
001  2    A        1           001-2-006
001  2    B        1           001-2-007
001  2    E        19          001-2-008
001  2    F        10          001-2-009
002  3    A        12          002-3-001（所影响的行数为 10 行）
*/

Answer 10

--2005
with a as
(
select serial,
id+'-'+ltrim(pr)+'-'+
right('000'+ltrim(ROW_NUMBER() OVER (partition by id order by id )),3) as se FROM 表
)
update a set serial = se 

Answer 11

还有一点忘了注明，用的是MS SQL Server 2000,另外PR相同的话，ID一定相同。

Answer 12

oracle中
create table os(id varchar(10),pr int,material varchar(10),qty int,serial varchar(20))
insert into os select '001',2,'A',2,null
insert into os select '001',2,'B',7,null
insert into os select '001',2,'A',10,null
insert into os select '001',2,'C',2,null
insert into os select '001',2,'D',1,null
insert into os select '001',2,'A',1,null
insert into os select '001',2,'B',1,null
insert into os select '001',2,'E',19,null
insert into os select '001',2,'F',10,null
insert into os select '002',3,'A',12,nullselect px=identity(int,1,1),* into # from osupdate os set serial=a.id+'-'+ltrim(a.pr)+'-'+
(select substr('000'||ltrim(count(1)),-3,3) from # where id=a.id and px<=a.px) 
from # a,os b
where a.id=b.id and a.pr=b.pr and a.material=b.material and a.qty=b.qtyselect * from osoracle中不支持right和left函数