如何在SQL server中对金额号负数和正数进行排序

Question

我有工作的Server.I，我有超过20000行使用server.I列文件名数量。在数量字段中插入负数和正数.Now我要排序数量字段负数和正数

例如：

Entity  ExpenseTypeCode ExpenseType  Amount

  11       043            Hotel        5

  12       044            travel       23

  13       045            drink        55

  14       046            Dinner       23

  15       047            airline      556

  16       048            Hotel        -5

我如何喜欢超过30000行.IN我的表，我有费用类型，但负和正值，我想排序我的表像负数和正序相同的值。

实体ExpenseTypeCode ExpenseType量

  11       043            Hotel        5

  16       048            Hotel        -5 --> Want sort like this 

  12       044            travel       23

  13       045            drink        55

  14       046            Dinner       23

  15       047            airline      556

我怎样才能对我的桌子进行liKE排序？

Answer 1

  select * from @t t 
    order by expensetype,
        case when amount > 0 then 1 else 2 end

结果

Entity      ExpenseTypeCode ExpenseType Amount
----------- --------------- ----------- -----------
         15              47 airline             556
         14              46 Dinner               23
         13              45 drink                55
         11              43 Hotel                 5
         16              48 Hotel                -5
         12              44 travel               23

如果你正在寻找匹配的配对，那么像这样的东西可能就是你想要的。

declare @t table(Entity int, ExpenseTypeCode int, ExpenseType varchar(10),  Amount int)
insert into @t values
(  11,       043,            'Hotel'   ,     6),
(  8,       043,            'Hotel'   ,     5),
(  9,       043,            'Hotel'   ,     5),
(  10,       043,            'Hotel'   ,     5),
(  12,       044,            'travel'  ,     23),
(  13,       045,            'drink'   ,     55),
(  14,       046,            'Dinner'  ,     23),
(  15,       047,            'airline' ,     556),
(  16,       048,            'Hotel'   ,     -5),
(  17,       048,            'Hotel'   ,     -5),
(  18,       043,            'Hotel'   ,     -6),
(  19,       043,            'Hotel'   ,     -6)

select t.*,row_number() over(partition by t.ExpenseType, t.amount order by t.entity) rn,t.amount as absamount
from @t t 
where t.amount > 0
union all
select t.*,row_number() over(partition by t.ExpenseTypeCode, t.amount order by t.entity) rn, abs(t.amount)
from @t t 
where t.amount < 0
order by t.expensetype,absamount,rn,t.amount desc

结果

Entity      ExpenseTypeCode ExpenseType Amount      rn                   absamount
----------- --------------- ----------- ----------- -------------------- -----------
         15              47 airline             556                    1         556
         14              46 Dinner               23                    1          23
         13              45 drink                55                    1          55
          8              43 Hotel                 5                    1           5
         16              48 Hotel                -5                    1           5
          9              43 Hotel                 5                    2           5
         17              48 Hotel                -5                    2           5
         10              43 Hotel                 5                    3           5
         11              43 Hotel                 6                    1           6
         18              43 Hotel                -6                    1           6
         19              43 Hotel                -6                    2           6
         12              44 travel               23                    1          23

也可能是完全加入

select  s.*,t.* from
(
select t.*,row_number() over(partition by t.ExpenseType, t.amount order by t.entity) rn
from @t t 
where t.amount > 0
) s
full join
(
select t.*,row_number() over(partition by t.ExpenseTypeCode, t.amount order by t.entity) rn
from @t t 
where t.amount < 0
) t on t.expensetype = s.expensetype and t.rn = s.rn and abs(t.amount) = s.amount
order by s.expensetype

Entity      ExpenseTypeCode ExpenseType Amount      rn                   Entity      ExpenseTypeCode ExpenseType Amount      rn
----------- --------------- ----------- ----------- -------------------- ----------- --------------- ----------- ----------- --------------------
       NULL            NULL NULL               NULL                 NULL          19              43 Hotel                -6                    2
         15              47 airline             556                    1        NULL            NULL NULL               NULL                 NULL
         14              46 Dinner               23                    1        NULL            NULL NULL               NULL                 NULL
         13              45 drink                55                    1        NULL            NULL NULL               NULL                 NULL
         11              43 Hotel                 6                    1          18              43 Hotel                -6                    1
         10              43 Hotel                 5                    3        NULL            NULL NULL               NULL                 NULL
          8              43 Hotel                 5                    1          16              48 Hotel                -5                    1
          9              43 Hotel                 5                    2          17              48 Hotel                -5                    2
         12              44 travel               23                    1        NULL            NULL NULL               NULL                 NULL

Answer 2

使用ABS函数对进行排序

ABS() :它将把你的负值转换成正数

SELECT
    *
FROM TableName 
Order BY ABS(Amount)

如果您想要负值和正值相同的和顺序，应该首先考虑正的，那么：

SELECT
    *
FROM TableName 
Order BY ABS(Amount),Amount*-1

示例：

初始

​

​

输出

​

​