为什么带有 FORMAT 的 MySQL 会忽略 ORDER? [英] Why is MySQL with FORMAT ignoring ORDER?
问题描述
第一个 MySQL 查询工作正常，但是，它没有被格式化为看起来像钱，只有美元.第二个格式正确，但它忽略了 ORDER 并且使 average_sales 不在 ORDER 中.使用 CONCACT 和 FORMAT 会导致问题吗?谢谢！
The first MySQL query works fine, however, it isn't formatted to look like money and with just dollars. The second one does the formatting correctly, but it is ignoring the ORDER and leaves average_sales not in ORDER. Could there be something with using CONCACT and FORMAT causing a problem? Thanks!
第一个，ORDER 是正确的:
First one, the ORDER is correct:
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.
But I've been asked to change it so that Average_Sales_by_Region shows dollar amounts with no cents. So the top figure of 20216.2162 needs to be $20,216.
所以我的第二个当它正确格式化钱时，它忽略了订单:
So my second one while it formats the money correctly, it ignores the ORDER:
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).谢谢！
I'm stuck here, I don't know why ORDER BY Average_Sales_by_Region DESC works in the first one, but it doesn't in the second one. The only difference is the use of CONCAT('$', FORMAT(AVG(sales), 0)) in place of AVG(sales). Thanks!
推荐答案
您仍然可以按未格式化的平均销售额进行订购.
You can still order by the unformatted average sales.
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;
这篇关于为什么带有 FORMAT 的 MySQL 会忽略 ORDER?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
