是这样吗: declare @t table(id int,v varchar(30)) insert into @t select 1, '57,127' union all select 2, '125,98' select id, v, max(cast(SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) as int)) max_value from @t t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.v,s.number,1) = ',' group by id,v /* id v max_value 2 125,98 125 1 57,127 127 */
需要先拆分,然后转化为数字类型,然后求max值
是这样吗: declare @t table(id int,v varchar(30)) insert into @t select 1, '57,127' union all select 2, '125,98' select id, v, max(cast(SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) as int)) max_value from @t t,master..spt_values s where s.number >=1 and s.type = 'P' and SUBSTRING(','+t.v,s.number,1) = ',' group by id,v /* id v max_value 2 125,98 125 1 57,127 127 */谢谢啦,已经解决了。
是这样吗:
declare @t table(id int,v varchar(30))
insert into @t
select 1, '57,127' union all
select 2, '125,98'
select id,
v,
max(cast(SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) as int)) max_value
from @t t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
group by id,v
/*
id v max_value
2 125,98 125
1 57,127 127
*/
是这样吗:
declare @t table(id int,v varchar(30))
insert into @t
select 1, '57,127' union all
select 2, '125,98'
select id,
v,
max(cast(SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) as int)) max_value
from @t t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
group by id,v
/*
id v max_value
2 125,98 125
1 57,127 127
*/谢谢啦,已经解决了。