向高手求助!如题!

Question

A                                B
008                             DFN(0202*0.50-0.50)
008                            DFN(0202*0.50-0.65)
006                            DFN(0202*0.75-0.50)
008                            DFN(0202*0.75-0.50) 
想要的结果:
C           A      B
008DFN001   008   DFN(0202*0.50-0.50)
008DFN002   008   DFN(0202*0.50-0.65)
006DFN001   008   DFN(0202*0.75-0.50)
008DFN003   008   DFN(0202*0.75-0.50)

Answer 1

--sql 2000用子查询完成。
create table tb(A varchar(3) , B varchar(20))
insert into tb values('008', 'DFN(0202*0.50-0.50)')
insert into tb values('008', 'DFN(0202*0.50-0.65)')
insert into tb values('006', 'DFN(0202*0.75-0.50)')
insert into tb values('008', 'DFN(0202*0.75-0.50)')
goselect c = a + left(b, charindex('(',b)-1) + right('00'+ cast((select count(1) from tb where A = t.a and b < t.b) + 1 as varchar),3), t.* from tb tdrop table tb/*
c                             A    B                    
----------------------------- ---- -------------------- 
008DFN001                     008  DFN(0202*0.50-0.50)
008DFN002                     008  DFN(0202*0.50-0.65)
006DFN001                     006  DFN(0202*0.75-0.50)
008DFN003                     008  DFN(0202*0.75-0.50)（所影响的行数为 4 行）
*/

Answer 2

--sql 2005用row_number()
create table tb(A varchar(3) , B varchar(20))
insert into tb values('008', 'DFN(0202*0.50-0.50)')
insert into tb values('008', 'DFN(0202*0.50-0.65)')
insert into tb values('006', 'DFN(0202*0.75-0.50)')
insert into tb values('008', 'DFN(0202*0.75-0.50)')
goselect c = a + left(b, charindex('(',b)-1) + right('00'+ cast(row_number() over(partition by a order by b) as varchar),3), t.* from tb tdrop table tb/*
c                          A    B
-------------------------- ---- --------------------
006DFN001                  006  DFN(0202*0.75-0.50)
008DFN001                  008  DFN(0202*0.50-0.50)
008DFN002                  008  DFN(0202*0.50-0.65)
008DFN003                  008  DFN(0202*0.75-0.50)(4 行受影响)
*/

Answer 3

-->*******************************************************
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang                             **********
-->CREATE TIME : 2010-11-19 16:11:56        **************
-->*******************************************************
--> 测试数据：tb
if object_id('tb') is not null
drop table tb---->建表
create table tb([A] varchar(3),[B] varchar(19))
insert tb
select '008','DFN(0202*0.50-0.50)' union all
select '008','DFN(0202*0.50-0.65)' union all
select '006','DFN(0202*0.75-0.50)' union all
select '008','DFN(0202*0.75-0.50)'--> 查询结果
SELECT * FROM tbSELECT *,A+left([B],3)+ right('0000'+convert (varchar(20),ROW_NUMBER() over(PARTITION by [A],left([B],4) order by [A])),3)  AS c
FROM tb--> 删除表格
DROP TABLE tb

Answer 4

结果是
008DFN001 008 DFN(0202*0.50-0.50)
008DFN002 008 DFN(0202*0.50-0.65)
006DFN001 006 DFN(0202*0.75-0.50)  ----- 不是008是006
008DFN003 008 DFN(0202*0.75-0.50)
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB
(
  COL1 VARCHAR(10),
  COL2 VARCHAR(50)
)INSERT INTO TB
SELECT '008','DFN(0202*0.50-0.50)' UNION ALL
SELECT '008','DFN(0202*0.50-0.65)' UNION ALL
SELECT '006','DFN(0202*0.75-0.50)' UNION ALL
SELECT '008','DFN(0202*0.75-0.50)' ;WITH CTE AS
( 
   SELECT ROW_NUMBER()OVER(PARTITION BY COL1 Order BY GETDATE()) AS ID,COL1,COL2 FROM TB
)
SELECT COL1+'DFN'+RIGHT('000'+CAST(ID AS VARCHAR(3)),3) AS ID,COL1,COL2 FROM CTE 
------------------
ID COL1 COL2
006DFN001 006 DFN(0202*0.75-0.50)
008DFN001 008 DFN(0202*0.75-0.50)
008DFN002 008 DFN(0202*0.50-0.50)
008DFN003 008 DFN(0202*0.50-0.65)

Answer 5

加个where charindex('(',b)-1 > 0限制一下，以免报错。
create table tb(A varchar(3) , B varchar(20))
insert into tb values('008', 'DFN(0202*0.50-0.50)')
insert into tb values('008', 'DFN(0202*0.50-0.65)')
insert into tb values('006', 'DFN(0202*0.75-0.50)')
insert into tb values('008', 'DFN(0202*0.75-0.50)')
go--sql 2000，假设你是按照A相同，B的大小来区分。
select c = a + left(b, charindex('(',b)-1) + right('00'+ cast((select count(1) from tb where A = t.a and b < t.b) + 1 as varchar),3), t.* from tb t
where charindex('(',b)-1) > 0drop table tb/*
c                             A    B                    
----------------------------- ---- -------------------- 
008DFN001                     008  DFN(0202*0.50-0.50)
008DFN002                     008  DFN(0202*0.50-0.65)
006DFN001                     006  DFN(0202*0.75-0.50)
008DFN003                     008  DFN(0202*0.75-0.50)（所影响的行数为 4 行）
*/
create table tb(A varchar(3) , B varchar(20))
insert into tb values('008', 'DFN(0202*0.50-0.50)')
insert into tb values('008', 'DFN(0202*0.50-0.65)')
insert into tb values('006', 'DFN(0202*0.75-0.50)')
insert into tb values('008', 'DFN(0202*0.75-0.50)')
go--sql 2005，假设你是按照A相同，B的大小来区分。
select c = a + left(b, charindex('(',b)-1) + right('00'+ cast(row_number() over(partition by a order by b) as varchar),3), t.* from tb t
where charindex('(',b)-1 > 0drop table tb/*
c                          A    B
-------------------------- ---- --------------------
006DFN001                  006  DFN(0202*0.75-0.50)
008DFN001                  008  DFN(0202*0.50-0.50)
008DFN002                  008  DFN(0202*0.50-0.65)
008DFN003                  008  DFN(0202*0.75-0.50)(4 行受影响)
*/