普适SQL忽略后继排序

Question

我有一个查询，我需要按销售代表然后客户(目前按字母顺序订购销售代表然后客户端)分组销售，但最近有人要求我必须按字母顺序对查询SalesRep进行排序，客户(根据销售额从最高到最低)排序，无论我在我的已知范围内做了什么，我都无法让查询做到这一点。它只在我所能看到的范围内继续按SalesRep进行排序。

我不知道这是否与团体有关，还是我做错了什么，但我正式迷失了方向。

    Select SM.Description as 'Sales Rep', CM.CustomerDesc, 
        sum(case when HL.ItemCode Not In ('111','112') then case when HL.DocumentType in (9,11) then HL.DiscountAmount else HL.DiscountAmount*(-1) end end) as 'Product Amount', 
        sum(case when HL.ItemCode in ('111','112') then case when HL.DocumentType in (9,11) then HL.DiscountAmount else HL.DiscountAmount*(-1) end end) as 'Transport Amount',
        max(CM.CashAccount) as CashAccount
from HistoryLines HL 
inner join SalesmanMaster SM on SM.Code = HL.SalesmanCode
inner join CustomerMaster CM on CM.CustomerCode = HL.CustomerCode
where`enter code here` HL.DocumentType in (8,9,11)
    and HL.DDate between '2018-07-01' and '2018-07-31'
group by SM.Description, CM.CustomerDesc
order by  SM.Description, 'Product Amount' DESC;

下面是上面的查询如何传递结果的示例：

Description |CustomerDesc   |Product Amount   |Transport    |Cash
A Sales Man |M Client       |17350            |3425         |0
A Sales Man |B Client       |6300             |1343         |1
B Sales Man |A Client       |8144             |1782         |0
B Sales Man |H Client       |45956.33         |13012.24     |0
B Sales Man |K Client       |34255.5          |2484         |0
B Sales Man |N Client       |96978.64         |14969.14     |0
B Sales Man |S Client       |139720.8         |0            |0
B Sales Man |TH Client      |25292.37         |17447.9      |0
B Sales Man |TY Client      |14809.6          |0            |0
B Sales Man |V Client       |11034            |6307.2       |0
C Sales Man |0 Client       |4590             |1350         |0
C Sales Man |AP Client      |23706            |3570         |0
C Sales Man |AR Client      |26106            |4950         |0
C Sales Man |BU Client      |54558.58         |0            |1
C Sales Man |C Client       |0                |0            |0
C Sales Man |CI Client      |27889.65         |4087.2       |0
C Sales Man |E Client       |8204.55          |1250         |0
C Sales Man |F Client       |72329.44         |17898        |0
C Sales Man |G Client       |4897.8           |1350         |0
C Sales Man |I Client       |15167.4          |2700         |0
C Sales Man |J Client       |274.8            |0            |0

预先感谢您的帮助。

Answer 1

在CASE子句中尝试使用完整的ORDER BY表达式：

ORDER BY
    SM.Description,
    SUM(CASE WHEN HL.ItemCode NOT IN ('111','112')
             THEN CASE WHEN HL.DocumentType IN (9,11)
                       THEN HL.DiscountAmount
                       ELSE HL.DiscountAmount*(-1) END
        END) DESC;

我假设您不能使用别名的原因是您的数据库不支持它。并非所有SQL数据库都支持在ORDER BY子句中使用别名。

我认为这里发生的事情是，您是按照字符串文本'Product Amount'排序的，也就是说，预期的别名只是被查看为字符串。这就是查询运行时没有错误的原因。

编辑：

根据您的注释/测试，您的数据库确实支持在ORDER BY子句中使用别名。但是，您实际上显示了一个字符串文本。可能有一种方法可以转义Product Amount来直接使用它。