>我有以下数据库:
+-------------+
| rd |
+-------------+
| 65000807:11 |
| 65000807:9 |
| 65000809:10 |
+-------------+
我想选择最大值,即65000807:11
(:
后的最大数字(。通过以下查询,我可以获取11
,但我需要获取整个字符串。我该怎么做?
SELECT MAX(CAST(SUBSTRING(rd,10,length(rd)-9) AS UNSIGNED)) AS 'rd' from myTable;
您可以在 order by 子句中使用子字符串并得到 1 个结果:
架构 (MySQL v8.0(
CREATE TABLE myTable (
`rd` VARCHAR(11)
);
INSERT INTO myTable
(`rd`)
VALUES
('65000807:11'),
('65000807:9'),
('65000809:10');
查询 #1
SELECT rd
FROM myTable
ORDER BY CAST(SUBSTRING(rd,10,length(rd)-9) AS UNSIGNED) DESC
LIMIT 1;
输出:
| rd |
| ----------- |
| 65000807:11 |
在DB Fiddle上查看
但是,我建议您重新考虑表的设计,您在同一列中存储了 2 条信息,这与使用 RDBMS 的目的背道而驰
如果第一个数字可以是可变长度的,你最好使用locate
:
select cast(substring(rd, locate(':', rd)+1) as signed)
from thetable
order by 1 desc
limit 1
只需使用substring_index()
和order by
:
select t.*
from t
order by substring_index(rd, ':', -1) + 0 desc
limit 1;