34,575
社区成员
发帖
与我相关
我的任务
分享
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) > 0
drop 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 > 0
drop 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 行受影响)
*/
结果是
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)
-->*******************************************************
-->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 tb
SELECT *,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
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
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
drop 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 行受影响)
*/
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
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
drop 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 行)
*/