sql - 如何根据以下条件排序查询结果？

假设我有一张桌子，如下所示：

employee | period  | commission
A        | 1Y      | 100
A        | 2Y      | 150
B        | 1Y      | 80
C        | 1Y      | 200
C        | 2Y      | 270
C        | 6M      | 80

此数据显示了员工根据时间段获得的佣金。
现在，我需要在node.js代码中获取以下格式的数据

[
  {
    "employee": "A",
    "commission": [
      {
        "period": "1Y",
        "commission": 100
      },
      {
        "period": "2Y",
        "commission": 150
      }
    ]
  },
  {
    "employee": "B",
    "commission": [
      {
        "period": "1Y",
        "commission": 80
      }
    ]
  },
  {
    "employee": "C",
    "commission": [
      {
        "period": "6M",
        "commission": 80
      },
      {
        "period": "1Y",
        "commission": 200
      },
      {
        "period": "2Y",
        "commission": 270
      }
    ]
  }
]

我写了以下问题

select
  employee,
  json_agg (json_build_object('period', period, 'commission', commission)) as commission
from
  table_name
  group by employee

这个查询给了我想要的结果，但现在我还想根据在特定时期内获得的佣金对员工进行排序
例如，根据在“1Y”期间获得的佣金对员工进行排序。我无法通过此查询找到解决方案。请为此类问题提供任何其他好的解决方案

最佳答案

提取查询中的一年佣金，并在包装查询中使用它对结果进行排序：

select employee, commission
from (
    select
        employee,
        json_agg (json_build_object('period', period, 'commission', commission)) as commission,
        max(case period when '1Y' then commission end) as one_year_commission
    from table_name
    group by employee
    ) s
order by one_year_commission