向高手求助!如题!

GUOCHENGJUN 2010-11-19 04:07:02
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)
...全文
71 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2010-11-19
  • 打赏
  • 举报
回复
加个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) > 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 行受影响)
*/
飘零一叶 2010-11-19
  • 打赏
  • 举报
回复
结果是
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)
王向飞 2010-11-19
  • 打赏
  • 举报
回复



-->*******************************************************
-->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

dawugui 2010-11-19
  • 打赏
  • 举报
回复
--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)')
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 行受影响)
*/
dawugui 2010-11-19
  • 打赏
  • 举报
回复
--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)')
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 行)
*/
飘零一叶 2010-11-19
  • 打赏
  • 举报
回复

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧