如何对包含年份的周数字段进行正确降序排序？

我有一张名为employee的表，仅包含date字段，数据如下：

date
52 week in 2021
23 week in 2022
34 week in 2021
1 week in 2022
52 week in 2022

执行select date from employee order by date desc后未得到预期排序结果，期望输出为：

date
52 week in 2022
23 week in 2022
1 week in 2022
52 week in 2021
34 week in 2021

date字段存储的是字符串类型，直接按字符串降序排序时，数据库会按照字符字典序比较，而非实际的年份、周数数值大小。比如字符串"52 week in 2021"会比"23 week in 2022"排在前面，因为首字符'5'的ASCII码大于'2'，不符合先按年份降序、同年份按周数降序的需求。

需要从字符串中提取年份和周数的数值，再基于这两个数值排序，不同数据库的实现语法略有差异：

MySQL #

SELECT date
FROM employee
ORDER BY 
    CAST(SUBSTRING_INDEX(date, ' ', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(date, ' ', 1) AS UNSIGNED) DESC;

PostgreSQL #

SELECT date
FROM employee
ORDER BY 
    split_part(date, ' ', 4)::INTEGER DESC,
    split_part(date, ' ', 1)::INTEGER DESC;

SQL Server #

SELECT date
FROM employee
ORDER BY 
    CAST(RIGHT(date, 4) AS INT) DESC,
    CAST(LEFT(date, CHARINDEX(' ', date) - 1) AS INT) DESC;

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