mysql - MySQL-JOIN-在另一个表中找到最大值，并从第一个表中显示客户名称

select discount.RATE, customer.NAME
from APP.DISCOUNT_CODE as discount
LEFT JOIN APP.CUSTOMER as customer
ON discount.DISCOUNT_CODE = customer.DISCOUNT_CODE;

当我使用此查询时，我会获得所有折扣率和客户名称，但是我只想显示一个具有最大Discount.RATE的客户。

我试过这个查询..

select max(discount.RATE), customer.NAME
from APP.DISCOUNT_CODE as discount
LEFT JOIN APP.CUSTOMER as customer
ON discount.DISCOUNT_CODE = customer.DISCOUNT_CODE;

但我得到一个错误..如何解决这个问题..

这是第一个查询的表。



错误是get正在使用max（discount.RATE）运行第二个查询，

[Exception, Error code 30,000, SQLState 42Y35] Column reference 'CUSTOMER.NAME' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.

最佳答案

SELECT
    discount.RATE, customer.NAME
    FROM
        APP.DISCOUNT_CODE as discount
        JOIN APP.CUSTOMER as customer ON discount.DISCOUNT_CODE = customer.DISCOUNT_CODE
    ORDER BY
        discount.RATE DESC
    LIMIT 1