22,207
社区成员
发帖
与我相关
我的任务
分享
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:module
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'module')
AND type in (N'U'))
DROP TABLE module
GO
---->建表
create table module([moduleCode] varchar(9),[moduleName] varchar(8))
insert module
select 'HELP','帮助中心' union all
select 'HELP001','帮助中心' union all
select 'HELP002','帮助中心' union all
select 'HELP003','帮助中心' union all
select 'HELP00101','帮助中心' union all
select 'HELP00102','帮助中心' union all
select 'HELP00103','帮助中心' union all
select 'HELP00104','帮助中心' union all
select 'HELP00105','帮助中心' union all
select 'HELP00106','帮助中心' union all
select 'HELP00107','帮助中心' union all
select 'HELP00108','帮助中心'
GO
--> 查询结果
SELECT top 1 *
FROM module order by len(moduleCode) desc,substring(moduleCode,5,len(moduleCode)-4) desc
--> 删除表格
--DROP TABLE module
try~~
select moduleCode, moduleName
from tb t
where not exists
(select 1 from (select max(right(moduleCode,3)) res from tb
where t.moduleName =moduleName
) m
where right(moduleCode,3)<m.res)
select max(ID),moduleCode , moduleName from (select *,row_number() over(order by moduleCode)as ID from module)
select max(row_number() over(order by moduleCode)) from module