背景
物料存在多个版本,我们每次只需要使用
最新的版本
问题
- 问题1:版本使用的是字符串,倒序排序,会导致
版本8 会排在版本11 前面
- 问题2:版本version字符串既有数字字符串,也有
字母字符串
解决思路
- 对于数字字符串,需要将其转换成整数,
- 字母字符串则转换成对应的
ASCII
码值
SQL语句
原本语句
SELECT
material_code AS material_code_in,
material_name AS material_name_in,
'' AS material_type_in,
material_class_small AS material_class_small_in,
material_class_big AS material_class_big_in,
erp_code AS erp_code_in,
version AS version_in,
unit AS unit_in
FROM tbl_material_info
WHERE material_code IN ('P120','P525A','2070039115')
改写语句
WITH RankedMaterials AS (
SELECT
material_code AS material_code_in,
material_name AS material_name_in,
'' AS material_type_in,
material_class_small AS material_class_small_in,
material_class_big AS material_class_big_in,
erp_code AS erp_code_in,
version AS version_in,
unit AS unit_in,
ROW_NUMBER() OVER (
PARTITION BY material_code
ORDER BY
CASE
WHEN TRY_CAST(version AS INT) IS NOT NULL THEN TRY_CAST(version AS INT)
ELSE ASCII(SUBSTRING(version, 1, 1)) -- 将字母字符串转换为对应的ASCII码值
END DESC
) AS rn
FROM tbl_material_info
WHERE material_code IN ('P120','P525A','2070039115')
)
SELECT
material_code_in,
material_name_in,
material_type_in,
material_class_small_in,
material_class_big_in,
erp_code_in,
version_in,
unit_in
FROM RankedMaterials
WHERE rn = 1;