mysql是否具有返回带有序数后缀的数字的功能? [英] Does mysql have function for returning a number with ordinal suffix?

问题描述

基本上我正在寻找类似的东西

Basically I'm looking for something like

SELECT ordinal(my_number) FROM my_table

将会返回

1st
11th
1071st
...
etc

但最好不使用存储过程

推荐答案

我不知道内置函数，但是很容易编写:

I don't know of a built-in function but it's pretty easy to write:

SELECT
  CONCAT(my_number, CASE
    WHEN my_number%100 BETWEEN 11 AND 13 THEN "th"
    WHEN my_number%10 = 1 THEN "st"
    WHEN my_number%10 = 2 THEN "nd"
    WHEN my_number%10 = 3 THEN "rd"
    ELSE "th"
  END)
FROM my_table;

这篇关于mysql是否具有返回带有序数后缀的数字的功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！