sql count case when 0 then

最新推荐文章于 2022-03-26 20:24:27 发布

yehuiling

最新推荐文章于 2022-03-26 20:24:27 发布

阅读量2.5k

点赞数

分类专栏：数据库相关文章标签： sql c

本文链接：https://blog.csdn.net/yehuiling/article/details/4061832

版权

数据库相关专栏收录该内容

4 篇文章 0 订阅

订阅专栏

//bug编号是否重复

select bug.bug_code,count(bug.bug_code) c from bug_record bug
group by bug.bug_code
having count(bug.bug_code)>1

//修改bug编号 sql拼

update bug_record bug set bug.bug_code=(
select '6-01_bug_' || to_char(to_number(substr(code,instr(code,'_')+1))+2) a from (
select substr(BUG_CODE,instr(BUG_CODE,'_')+1) code from Bug_Record where BUG_ID=(
select max(bug.BUG_ID) from Bug_Record bug where bug.BUG_CODE like '6-01_bug_%')
)
) where bug.bug_id='29079';
commit;

//生成bug编号

select ('24-01_bug_' || (select distinct case when (select count(1) from (
select to_char(to_number(substr(code,instr(code,'_')+1))+1) a from (
select substr(BUG_CODE,instr(BUG_CODE,'_')+1) code from Bug_Record where BUG_ID=(
select max(bug.BUG_ID)
from Bug_Record bug
where bug.BUG_CODE like '24-01_bug_%')
group by bug_code)
group by code))=0 then '1' else (
select to_char(to_number(substr(code,instr(code,'_')+1))+1) a from (
select substr(BUG_CODE,instr(BUG_CODE,'_')+1) code from Bug_Record where BUG_ID=(
select max(bug.BUG_ID)
from Bug_Record bug
where bug.BUG_CODE like '24-01_bug_%')
group by bug_code)
group by code) end as b from bug_record)) c from dual