在SQL中排序范围值 [英] ordering range values in SQL

问题描述

我目前在以下查询中遇到问题。



查询运行正常并返回正确的数据，但我的订单搞砸了



返回

0-50

101-200

201-300
301-500

1000

51-100



知道为什么会发生这种情况



这是我的疑问

i am currently having an issue on the following query.

The query runs fine and returns the correct data but my order by is messed up

it returns
0-50
101-200
201-300
301-500
1000
51-100

any idea why this could happen

this is my query

select
                case when amt >=0 and amt <50.00
                        then ' R0-R50'
                    when amt >=51.00 and amt <100.00
                        then 'R51-R100'
                    when amt >=101 and amt <200.00
                        then ' R101-R200'
                    when amt >=201 and amt <300.00
                        then ' R201-R300'
                    When amt >=301 and amt<501.00
                        then ' R301-R500'
                    When amt >=501 and amt < 1000.00
                        then ' R501-1000'
                            When amt >=1000
                        then '1000 and above'

                end [Range], count(*) [Number], sum(amt) [Total], round(avg(amt),2) [Average]
            from trxtable
            where status='Processed'
                and Program_ID = @ProgramID
                and amt > 0
                AND trxtable.Cd IN (SELECT Cd from Tblmem WHERE isnull(Comments, '') NOT LIKE 'TESTCARD')
                AND (CONVERT(CHAR(10),trxdate  ,120)) >= @startDate AND (CONVERT(CHAR(10),trxdate  ,120)) <= @endDate
            group by
                case when amt >=0 and amt <50.00
                        then ' R0-R50'
                    when amt >=51.00 and amt <100.00
                        then 'R51-R100'
                    when amt >=101 and amt <200.00
                        then ' R101-R200'
                    when amt >=201 and amt <300.00
                        then ' R201-R300'
                    When amt >=301 and amt<501.00
                        then ' R301-R500'
                    When amt >=501 and amt < 1000.00
                        then ' R501-1000'
                        When amt >=1000
                        then '1000 and above'
                end
            order by
                case when amt >=0 and amt <50.00
                        then ' R0-R50'
                    when amt >=51.00 and amt <100.00
                        then 'R51-R100'
                    when amt >=101 and amt <200.00
                        then ' R101-R200'
                    when amt >=201 and amt <300.00
                        then ' R201-R300'
                    When amt >=301 and amt<501.00
                        then ' R301-R500'
                    When amt >=501 and amt < 1000.00
                        then ' R501-1000'
                        When amt >=1000
                        then '1000 and above'
            end
    END

    SELECT @ErrorCode = 001
    SELECT @Errormessage='Successful'
END



GO

推荐答案

将订单条款更改为

ORDER BY Amt

更改订单依据子句包含一个会导致你想要的排序顺序的值。

Change the Order By clause to include a value that will cause the sort order you desire.

order by
    case when amt >=0 and amt <50.00
            then 1
        when amt >=51.00 and amt <100.00
            then 2
        when amt >=101 and amt <200.00
            then 3
        when amt >=201 and amt <300.00
            then 4
        When amt >=301 and amt<501.00
            then 5
        When amt >=501 and amt < 1000.00
            then 6
        When amt >=1000
            then 7
end

您可能还需要更改 Group By

这篇关于在SQL中排序范围值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！