如何将此查询转换为子查询sql

d7v8vwbk 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(476)

我怎样才能改变这个查询，我想把最大值变成一个子查询，当我输入sql时，唯一会出现的是id和名字。

SELECT staff.staff_id,staff.firstname,max(payment.amount)
from payment ,staff
where payment.staff_id=staff.staff_id

sql mysql subquery

来源：https://stackoverflow.com/questions/53645485/how-to-i-turn-this-query-to-a-sub-query-sql

3条答案

按热度按时间

kb5ga3dv1#

从 JOIN 到（相关）子查询：

SELECT staff.staff_id, staff.firstname,
       (SELECT MAX(payment.amount) 
        FROM payment
        WHERE payment.staff_id = staff.staff_id)
FROM staff

赞(0）回复(0）举报 2021-06-18

vltsax252#

使用显式联接

SELECT staff.staff_id,staff.firstname,max(payment.amount)
from payment join staff
on  payment.staff_id=staff.staff_id

但我想你想

SELECT staff.staff_id,staff.firstname,p.amount
    from payment p join staff
    on  p.staff_id=staff.staff_id
    where p.amount= select max(amount) from payment

赞(0）回复(0）举报 2021-06-18

ojsjcaue3#

可以尝试使用相关子查询

SELECT staff.staff_id,staff.firstname
from payment inner join staff
on payment.staff_id=staff.staff_id where payment.amount in (select max(payment.amount) from payment)

赞(0）回复(0）举报 2021-06-18

我来回答

如何将此查询转换为子查询sql

3条答案

相关问题

热门标签

最新问答