jQuery DataTables混合数据列排序优化：数字优先文本后置

当前使用num-fmt类型无法识别文本内容，导致排序时文本位置固定；需要实现数字类内容优先排序，文本始终后置的效果，可通过自定义DataTables排序类型解决：

解决方案步骤 #

1. 定义自定义排序规则，分别处理升序/降序逻辑：
   • 解析单元格内容，区分带货币符号、千分位的数字和纯文本
   • 强制数字始终排在文本之前
   • 数字之间按数值大小排序，文本之间按字符串规则排序
2. 在DataTables初始化时，将目标列指定为自定义排序类型

修改后完整代码 #

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.1/jquery.min.js" integrity="sha512-aVKKRRi/Q/YV+4mjoKBsE4x3H+BkegoM/em46NNlCqNTmUYADjBbeNefNxYV7giUp0VxICtqdrbqU7iVaeZNXA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css">

<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>

<script>
// 定义自定义排序类型：数字优先，文本后置
$.fn.dataTable.ext.type.order['currency-text-preceding-asc'] = function(a, b) {
  const parseVal = (val) => {
    const numMatch = val.match(/^\$?([\d,]+)$/);
    if (numMatch) {
      return { isNumber: true, value: parseFloat(numMatch[1].replace(/,/g, '')) };
    }
    return { isNumber: false, value: val };
  };
  
  const aData = parseVal(a);
  const bData = parseVal(b);
  
  // 数字优先于文本
  if (aData.isNumber && !bData.isNumber) return -1;
  if (!aData.isNumber && bData.isNumber) return 1;
  
  // 都是数字，按数值升序
  if (aData.isNumber) return aData.value - bData.value;
  
  // 都是文本，按字符串升序
  return a.localeCompare(b);
};

$.fn.dataTable.ext.type.order['currency-text-preceding-desc'] = function(a, b) {
  const parseVal = (val) => {
    const numMatch = val.match(/^\$?([\d,]+)$/);
    if (numMatch) {
      return { isNumber: true, value: parseFloat(numMatch[1].replace(/,/g, '')) };
    }
    return { isNumber: false, value: val };
  };
  
  const aData = parseVal(a);
  const bData = parseVal(b);
  
  // 数字优先于文本
  if (aData.isNumber && !bData.isNumber) return 1;
  if (!aData.isNumber && bData.isNumber) return -1;
  
  // 都是数字，按数值降序
  if (aData.isNumber) return bData.value - aData.value;
  
  // 都是文本，按字符串降序
  return b.localeCompare(a);
};

jQuery(document).ready(function() {
  jQuery("#postsummary").DataTable({
    paging: false,
    searching: false,
    columnDefs: [
      { type: "currency-text-preceding", targets: 1 }
    ]
  });
});
</script>

<table
  id="postsummary"
  class="dataTable no-footer"
  aria-describedby="postsummary_info"
>
  <thead>
    <tr>
      <th
        class="sorting"
        tabindex="0"
        aria-controls="postsummary"
        rowspan="1"
        colspan="1"
        aria-label="Name of Program: activate to sort column ascending"
        style="width: 573.075px"
      >
        Name of Program
      </th>
      <th
        class="sorting sorting_asc"
        tabindex="0"
        aria-controls="postsummary"
        rowspan="1"
        colspan="1"
        aria-label="Amount: activate to sort column descending"
        style="width: 95.9125px"
        aria-sort="ascending"
      >
        Amount
      </th>
      <th
        class="sorting"
        tabindex="0"
        aria-controls="postsummary"
        rowspan="1"
        colspan="1"
        aria-label="Type: activate to sort column ascending"
        style="width: 100.863px"
      >
        Type
      </th>
    </tr>
  </thead>
  <tbody>
    <tr class="odd">
      <td class="">
        Testing 1
      </td>
      <td class="sorting_1">varying</td>
      <td class="">Compensation</td>
    </tr>
    <tr class="even">
      <td class="">
        Testing 2
      </td>
      <td class="sorting_1">$50,000</td>
      <td class="">Grant</td>
    </tr>
    <tr class="odd">
      <td class="">
        Testing 3
      </td>
      <td class="sorting_1">$500,000</td>
      <td class="">Grant</td>
    </tr>
    <tr class="even">
      <td class="">
        Testing 4
      </td>
      <td class="sorting_1">varying</td>
      <td class="">Grant</td>
    </tr>
    <tr class="odd">
      <td class="">
        Testing 5
      </td>
      <td class="sorting_1">varying</td>
      <td class="">Grant</td>
    </tr>
    <tr class="even">
      <td class="">
        Testing 6
      </td>
      <td class="sorting_1">$3,500</td>
      <td class="">Grant</td>
    </tr>
  </tbody>
</table>

关键逻辑说明 #

• parseVal函数：提取货币格式字符串中的纯数字并转为数值，标记非数字内容
• 排序优先级：数字始终排在文本之前，升序/降序时仅调整数字内部排序顺序
• 兼容原有格式：前端显示内容保持不变，仅修改排序时使用的底层数据

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