求组,sql求最大值

[achuanok]

数据库中的数据如:
cgd-0802-10
cgd-0802-12
cgd-0802-13
cgd-0802-14
我要把字段分离出最后的整数10,12,13,14,然后求出最大值
高手帮帮忙
先谢了

[-狙击手-]

declare @t table(type varchar(13))


insert @t select 'cgd-0802-10'
insert @t select 'cgd-0802-12'
insert @t select 'cgd-0802-13'
insert @t select 'cgd-0802-14'


select PARSENAME(replace(type,'-','.'),1) as cnt from @t


select max(cnt) as maxcnt
from (select PARSENAME(replace(type,'-','.'),1) as cnt from @t) b
/*
cnt
--------------------------------------------------------------------------------------------------------------------------------
10
12
13
14

（所影响的行数为 4 行）

maxcnt
--------------------------------------------------------------------------------------------------------------------------------
14

（所影响的行数为 1 行）
*/

[-狙击手-]

declare @t table(type varchar(13)) 


insert @t select 'cgd-0802-10' 
insert @t select 'cgd-0802-12' 
insert @t select 'cgd-0802-13' 
insert @t select 'cgd-0802-14' 


select PARSENAME(replace(type,'-','.'),1) as cnt  from @t


select max(cnt) as maxcnt
from (select PARSENAME(replace(type,'-','.'),1) as cnt  from @t) b
/*
cnt                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------- 
10
12
13
14

（所影响的行数为 4 行）

     maxcnt                                                                                                                            
-------------------------------------------------------------------------------------------------------------------------------- 
14

（所影响的行数为 1 行）
*/

[青锋-SS]

create table #t(col varchar(200))
go
insert into #t select 'cgd-0802-10'
insert into #t select 'cgd-0802-12'
insert into #t select 'cgd-0802-13'
insert into #t select 'cgd-0802-14'
go
select max(reverse(left(reverse(col),charindex('-',reverse(col))-1))) from #t
go
drop table #t
go
--结果
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
14

(1 行受影响)

[mantti]

--Value-字段，Table-表
select max(Right(Value, 2)) as iMax from Table

[dawugui]

create table tb(col varchar(20))
insert into tb values('cgd-0802-10')
insert into tb values('cgd-0802-12')
insert into tb values('cgd-0802-13')
insert into tb values('cgd-0802-14')
go

select 最大值 = max(reverse(left(reverse(col),charindex('-',reverse(col)) - 1 ))) from tb

drop table tb

/*
最大值
--------------------
14

(1 行受影响)
*/

[achuanok]

兄弟们
不对头哦
如果数据里面
'cgd-0802-1'
'cgd-0802-3'
'cgd-0802-4'
'cgd-0802-5'
'cgd-0802-10'
'cgd-0802-12'
'cgd-0802-13'
select max(reverse(left(reverse(abc),charindex('-',reverse(abc)) - 1 ))) from aa
这个得不出来最大值13
而是5,怎么回事
如果只有
'cgd-0802-10'
'cgd-0802-12'
'cgd-0802-13'
这些数据就可以得到 最大值 13

[青锋-SS]

create table #t(col varchar(200))
go
insert into #t select 'cgd-0802-5'
insert into #t select 'cgd-0802-12'
insert into #t select 'cgd-0802-13'
insert into #t select 'cgd-0802-14'
go
select max(cast(reverse(left(reverse(col),charindex('-',reverse(col))-1)) as int)) from #t
go
drop table #t
go
--结果
-----------
14

(1 行受影响)

[青锋-SS]

转换成数字再max()就可以了.

[dawugui]

[Quote=引用 6 楼 achuanok 的回复:]
兄弟们
不对头哦
如果数据里面
'cgd-0802-1'
'cgd-0802-3'
'cgd-0802-4'
'cgd-0802-5'
'cgd-0802-10'
'cgd-0802-12'
'cgd-0802-13'
select max(reverse(left(reverse(abc),charindex('-',reverse(abc)) - 1 ))) from aa
这个得不出来最大值13
而是5,怎么回事
如果只有
'cgd-0802-10'
'cgd-0802-12'
'cgd-0802-13'
这些数据就可以得到 最大值 13
[/Quote]
--再转换一下.

create table tb(col varchar(20))
insert into tb values('cgd-0802-10')
insert into tb values('cgd-0802-12')
insert into tb values('cgd-0802-13')
insert into tb values('cgd-0802-14')
go

select 最大值 = max(cast(reverse(left(reverse(col),charindex('-',reverse(col)) - 1 ))as bigint)) from tb

drop table tb

/*
最大值
--------------------
14

(1 行受影响)
*/

[-狙击手-]

declare @t table(type varchar(13)) 


insert @t select 'cgd-0802-1' 
insert @t select 'cgd-0802-2' 
insert @t select 'cgd-0802-5' 
insert @t select 'cgd-0802-14' 


select PARSENAME(replace(type,'-','.'),1) as cnt  from @t


select max(cast(cnt as int)) as maxcnt
from (select PARSENAME(replace(type,'-','.'),1) as cnt  from @t) b
/*
cnt                                                                                                                              
-------------------------------------------------------------------------------------------------------------------------------- 
1
2
5
14

maxcnt      
----------- 
14


*/

[pengxuan]

create table a(a1 varchar(20))
go
insert into a
select 'cgd-0802-10' union all
select 'cgd-0802-12' union all
select 'cgd-0802-13' union all
select 'cgd-0802-14'

select * from a

select max(right(a1,2)) from a

[wangxuelid]

declare @t table(type varchar(13)) 
insert @t select 'cgd-0802-10' 
insert @t select 'cgd-0802-12' 
insert @t select 'cgd-0802-13' 
insert @t select 'cgd-0802-14' 
select  max(reverse(left(reverse(type),charindex('-',reverse(type))-1))) as typae   from @t

[wangxuelid]

/*
type
14

*/

[ojuju10]

declare @t table(type varchar(13)) 


insert @t select 'cgd-0802-10' 
insert @t select 'cgd-0802-12' 
insert @t select 'cgd-0802-13' 
insert @t select 'cgd-0802-14' 

select max(substring(substring(type,charindex('-',type)+1,len(type)),charindex('-',substring(type,charindex('-',type)+1,len(type)))+1,len(type))) from @t

--
14

[conan304]

declare @t table(col varchar(50))
insert @t select 'cgd-0802-10' 
union all select 'cgd-0802-12' 
union all select 'cgd-0802-13' 
union all select 'cgd-0802-14'

select right(col,2) from @t
where col=(select max(col) from @t) 

/*
---- 
14
*/