sql - SQL 中的 ORDER BY，其中列是带有嵌入整数的合成字符串

Question

我有下表:

CREATE TABLE TEST 
(
     name VARCHAR(10), 
     date_of_entry DATE, 
     flag1 INT, 
     flag2 INT,
     salary FLOAT, 
     flag3 INT,
     id INT
);

具有以下行:

name    date_of_entry   flag1   flag2   salary      flag3   id
--------------------------------------------------------------
AGMA    2018-11-08      0       1       265466940   1       1
AGMA    2018-11-08      0       1       220737125   1       2
AGMA    2018-11-08      0       1       181270493   0       3
AGMA    2018-11-08      0       1       8584205     0       4

我想执行以下 SQL 以特定方式对行进行排序:

SELECT 
    name
    + '.' + CONVERT(varchar(8), date_of_entry, 112) 
    + '.' + CONVERT(varchar(1), flag1)
    + '.' + CONVERT(varchar(1), flag2)
    + '.' + CONVERT(varchar(2555), salary)
    + '.' + CONVERT(varchar(1), flag3)
    + '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER
FROM 
    TEST
ORDER BY 
    SYNTHETIC_ORDER DESC

但是，salary 列在字符串中的排序不正确。所以我的最终结果是(在 Microsoft SQL Server 中执行时):

SYNTHETIC_ORDER
-----------------------------------
AGMA.20181108.0.1.8.58421e+006.0.4
AGMA.20181108.0.1.2.65467e+008.1.1
AGMA.20181108.0.1.2.20737e+008.1.2
AGMA.20181108.0.1.1.8127e+008.0.3

可以注意到，结果是 id 4 排在第一位，而我希望 id 1 排在第一位。

预期结果:

SYNTHETIC_ORDER
-----------------------------------
AGMA.20181108.0.1.2.65467e+008.1.1
AGMA.20181108.0.1.2.20737e+008.1.2
AGMA.20181108.0.1.1.8127e+008.0.3
AGMA.20181108.0.1.8.58421e+006.0.4

有没有办法确保薪水在此 SQL 中正确排序？

Answer 1

为什么不能按各个列排序？

SELECT 
    date_of_entry, flag1, flag2, salary, flag3 
    , name
        + '.' + CONVERT(varchar(8), date_of_entry, 112) 
        + '.' + CONVERT(varchar(1), flag1)
        + '.' + CONVERT(varchar(1), flag2)
        + '.' + CONVERT(varchar(2555), salary)
        + '.' + CONVERT(varchar(1), flag3)
        + '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER
FROM  TEST
ORDER BY date_of_entry DESC, flag1 DESC, flag2 DESC, salary DESC, flag3 DESC

这将使您达到 MAX。

SELECT SYNTHETIC_ORDER
FROM (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY date_of_entry DESC, flag1 DESC, flag2 DESC, salary DESC, flag3 DESC) AS RowNum
        , name
            + '.' + CONVERT(varchar(8), date_of_entry, 112) 
            + '.' + CONVERT(varchar(1), flag1)
            + '.' + CONVERT(varchar(1), flag2)
            + '.' + CONVERT(varchar(2555), salary)
            + '.' + CONVERT(varchar(1), flag3)
            + '.' + CONVERT(varchar(1), id) AS SYNTHETIC_ORDER
    FROM  TEST
) a
WHERE RowNum = 1