Pervasive SQL中如何按BIN字段子串实现特定分组排序？

需求说明 #

需要对零件存储位（BIN字段，格式如GS7A01，其中7为货架位置，A-E为楼层，01为货架编号）进行特殊排序：

1. 先按货架位置（如GS7A01中的7）归类
2. 再按货架编号（末尾两位数字，如01、02）排序，即先展示所有以01结尾的BIN记录，再依次展示02、03等
3. 同货架编号下按楼层（A到E）排序，最后按零件号（PART）排序

现有问题 #

当前查询的排序逻辑不符合需求，尝试按BIN子串分组时报错（要求必须按BIN字段分组，但添加BIN后无法按子串实现预期排序）。使用数据库为Pervasive SQL（与MySQL语法相近但有差异）。

现有查询语句 #

SELECT vim.BIN, vim.PART, vinvm.DESCRIPTION
FROM   V_ITEM_MASTER vim
LEFT OUTER JOIN V_INVENTORY_MSTR vinvm ON (vim.PART = vinvm.PART) AND (vim.LOCATION = vinvm.LOCATION)
WHERE  vim.LOCATION = 'HN' and vim.BIN like 'GS%'
ORDER BY substring(vim.bin,1,4),substring(vim.bin,5,2), vim.PART

当前结果 #

期望结果 #

解决方案 #

1. 通过Pervasive SQL直接实现 #

不需要使用GROUP BY，只需调整ORDER BY的排序逻辑即可满足需求。核心是按货架位置 → 货架编号 → 楼层 → 零件号的顺序排序：

SELECT vim.BIN, vim.PART, vinvm.DESCRIPTION
FROM   V_ITEM_MASTER vim
LEFT OUTER JOIN V_INVENTORY_MSTR vinvm ON (vim.PART = vinvm.PART) AND (vim.LOCATION = vinvm.LOCATION)
WHERE  vim.LOCATION = 'HN' and vim.BIN like 'GS%'
-- 排序逻辑：货架位置（第3位字符）→ 货架编号（末尾两位）→ 楼层（第4位字符）→ 零件号
ORDER BY 
    SUBSTRING(vim.BIN, 3, 1), 
    SUBSTRING(vim.BIN, 5, 2), 
    SUBSTRING(vim.BIN, 4, 1), 
    vim.PART

逻辑说明

• SUBSTRING(vim.BIN, 3, 1)：提取货架位置（如GS7A01中的7），确保同一货架的记录放在一起
• SUBSTRING(vim.BIN, 5, 2)：提取末尾两位货架编号（如01、02），实现先展示所有01结尾的记录，再依次展示02、03等
• SUBSTRING(vim.BIN, 4, 1)：提取楼层（如A、B），确保同编号下按底层到顶层排序
• vim.PART：最后按零件号排序

Pervasive SQL支持SUBSTRING函数，语法与MySQL兼容，因此该语句可以直接运行。

2. 备选：.NET ERP系统自定义脚本 #

如果因数据库权限或函数限制导致SQL无法实现，可在.NET架构的ERP系统中通过以下步骤处理：

1. 用原查询获取所有数据
2. 在内存中对数据进行排序：
   • 先按BIN的第3位字符归类
   • 再按BIN的末尾两位字符串排序
   • 最后按BIN的第4位字符和PART排序
3. 将排序后的数据生成报表

来源说明 #

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