SQL Server中Varchar类型英寸尺寸列排序异常问题求助

这个问题很典型——字符串排序是按字符顺序来的，而你需要的是实际数值大小的排序。核心思路就是把Size列的字符串转换成对应的十进制数值，再按这个数值排序。下面分几种常见数据库给出具体方案：

MySQL 解决方案 #

SELECT * FROM TB_Items
ORDER BY 
  CASE 
    -- 判断是否是分数格式（包含/）
    WHEN INSTR(REPLACE(Size, '"', ''), '/') > 0 
    THEN 
      -- 拆分分子分母，转成decimal后计算比值
      CAST(SUBSTRING_INDEX(REPLACE(Size, '"', ''), '/', 1) AS DECIMAL(10,4)) 
      / 
      CAST(SUBSTRING_INDEX(REPLACE(Size, '"', ''), '/', -1) AS DECIMAL(10,4))
    -- 整数格式直接转成decimal
    ELSE CAST(REPLACE(Size, '"', '') AS DECIMAL(10,4))
  END ASC;

SQL Server 解决方案 #

SELECT * FROM TB_Items
ORDER BY 
  CASE 
    WHEN CHARINDEX('/', REPLACE(Size, '"', '')) > 0 
    THEN 
      CAST(LEFT(REPLACE(Size, '"', ''), CHARINDEX('/', REPLACE(Size, '"', '')) - 1) AS DECIMAL(10,4))
      /
      CAST(RIGHT(REPLACE(Size, '"', ''), LEN(REPLACE(Size, '"', '')) - CHARINDEX('/', REPLACE(Size, '"', ''))) AS DECIMAL(10,4))
    ELSE CAST(REPLACE(Size, '"', '') AS DECIMAL(10,4))
  END ASC;

PostgreSQL 解决方案 #

SELECT * FROM TB_Items
ORDER BY 
  CASE 
    WHEN POSITION('/' IN REPLACE(Size, '"', '')) > 0 
    THEN 
      (SPLIT_PART(REPLACE(Size, '"', ''), '/', 1)::DECIMAL(10,4))
      /
      (SPLIT_PART(REPLACE(Size, '"', ''), '/', 2)::DECIMAL(10,4))
    ELSE REPLACE(Size, '"', '')::DECIMAL(10,4)
  END ASC;

优化建议（数据量大时） #

如果你的表数据量比较大，每次查询都计算转换会影响性能。可以给表添加一个存储计算列，提前把转换后的数值存起来：
以MySQL为例：

ALTER TABLE TB_Items ADD COLUMN Size_Numeric DECIMAL(10,4) GENERATED ALWAYS AS (
  CASE 
    WHEN INSTR(REPLACE(Size, '"', ''), '/') > 0 
    THEN CAST(SUBSTRING_INDEX(REPLACE(Size, '"', ''), '/', 1) AS DECIMAL(10,4)) / CAST(SUBSTRING_INDEX(REPLACE(Size, '"', ''), '/', -1) AS DECIMAL(10,4))
    ELSE CAST(REPLACE(Size, '"', '') AS DECIMAL(10,4))
  END
) STORED;

之后查询直接用：

SELECT * FROM TB_Items ORDER BY Size_Numeric ASC;

注意事项 #

• 如果Size值里有多余空格（比如" 5/8 "），记得先加TRIM处理，比如REPLACE(TRIM(Size), '"', '')
• 确保分母不为0（你的场景里应该不会出现，但如果有异常值需要额外判断）

内容的提问来源于stack exchange，提问作者Faisal