主页 / dba / 问题 / 223627
Accepted
kn3l
Asked: 2018-11-29 01:58:04 +0800 CST

通过将其他列分组来对特定列进行排序

  • 772
SELECT 
      CurrencyCodeFrom,
      CurrencyCodeTo, 
      MidRevalRate,
      MultiplierRate,
      RevalRate,
      CurrencyMarketCode,
      Added, 
      Modified, 
      UpdateReason
FROM 
     [InsightWarehouse].[dbo].[DimCurrency] as dc
 INNER  JOIN 
     [InsightWarehouse].[dbo].[FactCurrency] as fc 
     ON fc.CurrencyId = dc.CurrencyId
     AND fc.BusinessDate  = '2018-11-27'
     AND ( CurrencyCodeFrom <> 'KHR' OR CurrencyCodeTo <> 'KHR')

结果

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode
THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.750000000   122.750000000   1.000000000 10
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9

我想CurrencyMarketCode通过保持分组来对 DESC进行排序CurrencyCodeFrom CurrencyCodeTo

应该

CurrencyCodeFrom    CurrencyCodeTo  MidRevalRate    MultiplierRate  RevalRate   CurrencyMarketCode

THB                 KHR             122.500000000   122.500000000   1.000000000 1
THB                 KHR             122.500000000   122.500000000   1.000000000 3
THB                 KHR             122.750000000   122.750000000   1.000000000 5
THB                 KHR             122.750000000   122.750000000   1.000000000 7
THB                 KHR             122.750000000   122.750000000   1.000000000 9
THB                 KHR             122.750000000   122.750000000   1.000000000 10

USD                 KHR             4035.000000000  4035.000000000  1.000000000 1
USD                 KHR             4035.000000000  4035.000000000  1.000000000 3
USD                 KHR             4047.500000000  4047.500000000  1.000000000 5
USD                 KHR             4047.500000000  4047.500000000  1.000000000 7
USD                 KHR             4047.500000000  4047.500000000  1.000000000 9
USD                 KHR             4047.500000000  4047.500000000  1.000000000 10
sql-server

1 个回答

  1. Best Answer

    你的评论

    我想通过将 CurrencyMarketCode DESC 保留为 CurrencyCodeFrom CurrencyCodeTo 组来对它进行排序

    与您的结果不匹配，因为CurrencyMarketCode您的结果是按升序排列的。

    正如@ypercube 评论的那样，为什么不简单

    ORDER BY CurrencyCodeFrom, CurrencyCodeTo, CurrencyMarketCode解决你的问题？

    --demo setup
drop table if exists table1
go
CREATE TABLE Table1 (
    [CurrencyCodeFrom] VARCHAR(3)
    ,[CurrencyCodeTo] VARCHAR(3)
    ,[MidRevalRate] DECIMAL(20, 9)
    ,[MultiplierRate] DECIMAL(20, 9)
    ,[RevalRate] DECIMAL(20, 9)
    ,[CurrencyMarketCode] int
    );    
INSERT INTO Table1
    ([CurrencyCodeFrom], [CurrencyCodeTo], [MidRevalRate], [MultiplierRate], [RevalRate], [CurrencyMarketCode])
VALUES
    ('THB', 'KHR', 122.500000000, 122.500000000, 1.000000000, 1),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 10),
    ('THB', 'KHR', 122.500000000, 122.500000000, 1.000000000, 3),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 5),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 7),
    ('THB', 'KHR', 122.750000000, 122.750000000, 1.000000000, 9),
    ('USD', 'KHR', 4035.000000000, 4035.000000000, 1.000000000, 1),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 10),
    ('USD', 'KHR', 4035.000000000, 4035.000000000, 1.000000000, 3),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 5),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 7),
    ('USD', 'KHR', 4047.500000000, 4047.500000000, 1.000000000, 9)
;

SELECT *
FROM table1
ORDER BY CurrencyCodeFrom
    ,CurrencyCodeTo
    ,CurrencyMarketCode;

    结果似乎与您想要的结果相符。

    | CurrencyCodeFrom | CurrencyCodeTo | MidRevalRate   | MultiplierRate | RevalRate   | CurrencyMarketCode |
|------------------|----------------|----------------|----------------|-------------|--------------------|
| THB              | KHR            | 122.500000000  | 122.500000000  | 1.000000000 | 1                  |
| THB              | KHR            | 122.500000000  | 122.500000000  | 1.000000000 | 3                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 5                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 7                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 9                  |
| THB              | KHR            | 122.750000000  | 122.750000000  | 1.000000000 | 10                 |
| USD              | KHR            | 4035.000000000 | 4035.000000000 | 1.000000000 | 1                  |
| USD              | KHR            | 4035.000000000 | 4035.000000000 | 1.000000000 | 3                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 5                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 7                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 9                  |
| USD              | KHR            | 4047.500000000 | 4047.500000000 | 1.000000000 | 10                 |
    • 3

相关问题