使用order by和group by函数以及聚合字段排序sql查询结果时遇到问题

u2nhd7ah  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(316)

以下查询的结果遇到了一些问题。
我的目的是显示主机收到的总付款的结果,根据汇总列(别名为“收到的总金额($)”按降序排序)

Select   H.host_name AS 'Host Name', 
         H.location AS 'District', 
         AVG(F.host_feedback_rating) AS 'Average Rating',
         SUM(P.payment_amount) AS 'Total Amount Received ($)'

FROM booking B, host H, payments P, feedback F 
WHERE H.host_id = P.host_id
AND F.booking_code = B.booking_code
AND F.host_id = H.host_id
AND P.payment_date  between '2018/01/01' and '2018/12/31'

GROUP BY 1,2
ORDER BY 'Total Amount Received ($)' DESC;

当前结果:

Host Name   District    Average Rating  Total Amount Received ($)
John         Detroit              5                275
Leeroy       Chicago              5                50
Rinoa        Texas                5                225
Sally        California           4                45

我尝试过调整GROUPBY子句,但不知何故,接收的总金额($)没有正确排序。。。你能给我介绍一下吗?
非常感谢你!

o2g1uqev

o2g1uqev1#

试着用backtick-like

...ORDER BY `Total Amount Received ($)` DESC

或者

...Order By 4 DESC

或者

...SUM(P.payment_amount) DESC

谢谢:)

btxsgosb

btxsgosb2#

'Total Amount Received ($)' 用单引号括起来( ' ),因此是一个常量字符串文本。虽然根据它进行排序在技术上并没有错(正如您所看到的-没有错误),但这毫无意义,因为所有行的常量值都相同。
最简单的方法imho是根据列的索引而不是别名进行排序:

ORDER BY 4 DESC

相关问题