以下查询的结果遇到了一些问题。
我的目的是显示主机收到的总付款的结果,根据汇总列(别名为“收到的总金额($)”按降序排序)
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子句,但不知何故,接收的总金额($)没有正确排序。。。你能给我介绍一下吗?
非常感谢你!
2条答案
按热度按时间o2g1uqev1#
试着用backtick-like
或者
或者
谢谢:)
btxsgosb2#
'Total Amount Received ($)'
用单引号括起来('
),因此是一个常量字符串文本。虽然根据它进行排序在技术上并没有错(正如您所看到的-没有错误),但这毫无意义,因为所有行的常量值都相同。最简单的方法imho是根据列的索引而不是别名进行排序: