34,588
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test]([PRD_NO] varchar(4))
insert [test]
select 'abc' union all
select 'abcR' union all
select 'cde' union all
select 'cdeR' union all
select 'tyt' union all
select 'tytR' union all
select 'gfh' union all
select 'fgg' union all
select 'def' union all
select 'defR'
update test
set [PRD_NO]=[PRD_NO]+'R'
where exists(select 1 from test b where test.PRD_NO=left(b.PRD_NO,LEN(b.PRD_NO)-1))
select * from test
/*
PRD_NO
-----------
abcR
abcR
cdeR
cdeR
tytR
tytR
gfh
fgg
defR
defR
*/
--你看看这个结果对不?
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test]([PRD_NO] varchar(4))
insert [test]
select 'abc' union all
select 'abcR' union all
select 'cde' union all
select 'cdeR' union all
select 'tyt' union all
select 'tytR' union all
select 'gfh' union all
select 'fgg' union all
select 'def' union all
select 'defR'
select [PRD_NO]+'R' as [PRD_NO]
from test a
where exists(select 1 from test b where a.PRD_NO=left(b.PRD_NO,LEN(b.PRD_NO)-1))
/*
PRD_NO
-----------
abcR
cdeR
tytR
defR
*/
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([PRD_NO] varchar(4))
insert [test]
select 'abc' union all
select 'abcR' union all
select 'cde' union all
select 'cdeR' union all
select 'def' union all
select 'defR'
update test
set [PRD_NO]=[PRD_NO]+'R'
where CHARINDEX('R',[PRD_NO])=0
select * from test
/*
PRD_NO
-------------------
abcR
abcR
cdeR
cdeR
defR
defR
*/
--真心不明白你说的是什么意思。给出测试数据最好给出自己期待的结果
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([PRD_NO] varchar(4))
insert [test]
select 'abc' union all
select 'abcR' union all
select 'cde' union all
select 'cdeR' union all
select 'def' union all
select 'defR'
select case when right([PRD_NO],1)='R' then LEFT([PRD_NO],LEN([PRD_NO])-1)
else [PRD_NO] end as [PRD_NO] from test
/*
PRD_NO
---------------
abc
abc
cde
cde
def
def
*/