MySQL 通过对数字范围进行分类排序，然后按字母顺序按各个类别排序 - mysql ordering by categorizing number range, then alphabetically in individual categories

mysql ordering by categorizing number range, then alphabetically in individual categories

我有一个按美元金额列排序的团队列表，当 PHP 在我的页面上发布它时，它有一个级别/类别标记为每个团队，具体取决于每个团队的金额是什么等级。例：

SQL 结果：

Team | Amount
  A    550.00
  B    1200.00
  C    320.00
  D    800.00

PHP 的布局如下：

B - Top tier (over $1,000)
D - Middle tier (over $500)
A - Middle tier (over $500)
C - Low tier (over $150)

现在我在页面上显示了很多团队，我需要做的是他们仍然按照页面上从最高到最低的数量顺序显示，我实际上需要 SQL 输出到他们的层中，然后在每一层中按字母顺序排序。例如（使用我上面的小 PHP 输出示例），团队"D"和"A"都在同一层中，但"A"应该首先按字母顺序显示。

使用：

SELECT CASE WHEN Amount > 1000 THEN 4
            WHEN Amount > 500 THEN 3
            WHEN Amount > 150 THEN 2
            ELSE 1
       END Tier,
       Team, Amount
FROM Table
ORDER BY Tier DESC, Team

select teamname from (
    select teamname, 1 as ordern from teams where Amount>=1000
        union all
    select teamname, 2 from teams where Amount<1000 and Amount>=500
        union all
    select teamname, 3 from teams where Amount<500 and Amount>=150
        union all
    select teamname, 4 from teams where Amount<150
) as q
order by ordern DESC, teamname ASC

您需要

首先按数量排序，其次按团队排序。

select TeamName,'Top tier (over $1,000)'
from table where amount > 1000
union all
select TeamName,'Middle tier (over $500)'
from table where amount between 500 and 1000
union all
select TeamName,'Low tier (over $150)'
from table where amount between 150 and 500
order by 2,1