mysql - 为什么使用 FORMAT 的 MySQL 会忽略 ORDER？

Question

第一个 MySQL 查询工作正常，但是，它的格式看起来不像钱，而且只有美元。第二个格式正确，但它忽略了 ORDER 并使 average_sales 不在 ORDER 中。使用 CONCACT 和 FORMAT 会不会导致问题？谢谢!

第一个，ORDER是正确的:

select Region,AVG(sales) as 'Average_Sales_by_Region', count(*) as '# of Dist in state'
from dist, Regions_US
where dist.state=Regions_US.State
group by Region
ORDER BY Average_Sales_by_Region DESC;

+--------------------+-------------------------------+-------------------------+
| Region             | Average_Sales_by_Region       | # of Dist in state      |
+--------------------+-------------------------------+-------------------------+
| Mountain           |                    20216.2162 |                      74 |
| West North Central |                    18267.5000 |                      40 |
| South Atlantic     |                    16225.2809 |                     178 |
| East South Central |                    14966.6667 |                      30 |
| West South Central |                    13704.3840 |                     125 |
| East North Central |                    12668.3544 |                      79 |
| New England        |                    11915.6250 |                      32 |
| Pacific            |                    11552.8083 |                     120 |
| Middle Atlantic    |                    10291.6031 |                     131 |
| Alaska-Hawaii      |                     8150.0000 |                       4 |
+--------------------+-------------------------------+-------------------------+

但我被要求更改它，以便 Average_Sales_by_Region 显示没有美分的美元金额。所以 20216.2162 的最高数字需要为 $20,216。

所以我的第二个虽然它正确地格式化了钱，但它忽略了订单:

select Region,CONCAT('$', FORMAT(AVG(sales), 0)) as 'Average_Sales_by_Region', count(*) as '# of Dist in state'
from dist, Regions_US
where dist.state=Regions_US.State
group by Region
ORDER BY Average_Sales_by_Region DESC;

+--------------------+-------------------------------+-------------------------+
| Region             | Average_Sales_by_Region       | # of Dist in state      |
+--------------------+-------------------------------+-------------------------+
| Alaska-Hawaii      | $8,150                        |                       4 |
| Mountain           | $20,216                       |                      74 |
| West North Central | $18,268                       |                      40 |
| South Atlantic     | $16,225                       |                     178 |
| East South Central | $14,967                       |                      30 |
| West South Central | $13,704                       |                     125 |
| East North Central | $12,668                       |                      79 |
| New England        | $11,916                       |                      32 |
| Pacific            | $11,553                       |                     120 |
| Middle Atlantic    | $10,292                       |                     131 |
+--------------------+-------------------------------+-------------------------+

我被困在这里，我不知道为什么 ORDER BY Average_Sales_by_Region DESC 在第一个中有效，但在第二个中无效。唯一的区别是使用 CONCAT('$', FORMAT(AVG(sales), 0)) 代替 AVG(sales)。谢谢!

Answer 1

尝试使用这个查询

select Region,CONCAT('$', FORMAT(AVG(sales), 0)) as 'Average_Sales_by_Region', count(*) as '# of Dist in state'
from dist, Regions_US
where dist.state=Regions_US.State
group by Region ORDER BY AVG(sales) DESC

通过这种方式，您可以像第一个查询一样订购，但像第二个查询一样显示结果