例如,我有下表:
pc | cd
---------------
pc0 | 4x
pc1 | 24x
pc2 | 8x
pc3 | 4x
pc4 | 24x
我需要得到这样的东西:
cd_max
--------
24x
或对其进行排序:
pc | cd
---------------
pc0 | 4x
pc3 | 4x
pc2 | 8x
pc1 | 24x
pc4 | 24x
"24x"显然是字符串,但我需要在其中获取最大/最小整数。
我正在使用MS SQL Server。
如果假设字符串总是以x
结尾是可以的,我会切断它,将字符串转换为数字,找到最大值并重新敲打x
:
SELECT MAX(CAST(LEFT(cd, LEN(cd) - 1) AS INT)) + 'x'
FROM mytable
尾随x
的切片,并将varchar
转换为int
,如下所示:
cast(left(cd, len(cd) - 1) as int)
现在,您可以按此值排序并选择最大的值:
select top 1 cd as cd_max
from my_table
order by cast(left(cd, len(cd) - 1) as int) desc
您可以尝试填充'x'
只保留int.比较或获取最大值。
SELECT CONCAT(MAX(CAST(REPLACE(cd,'x','') as int)) , 'x') cd_max
FROM T
或
SELECT *
FROM T
ORDER BY CAST(REPLACE(cd,'x','') AS INT)
schema:
create table Detail (pc varchar(100) ,cd varchar(100) );
insert into Detail values ('pc0','4x');
insert into Detail values ('pc1','24x');
insert into Detail values ('pc2','8x');
insert into Detail values ('pc3','4x');
insert into Detail values ('pc4','24x');
sql:我假设只有最后一个字符不是数字
select * from Detail order by cast(left(cd,len(cd)-1) as int)
输出:
pc cd
pc0 4x
pc3 4x
pc2 8x
pc4 24x
pc1 24x
SQL2:获取最大 CD
select top(1) cd as cd_max from Detail order by cast(left(cd,len(cd)-1) as int) desc
输出:
cd_max
24x