admin

从另一个查询中获取最大值

sql

我有一些查询问题。我需要从该查询中获取最大值和product_name:

select
    products.product_name,
    sum(product_invoice.product_amount) as total_amount
from 
    product_invoice
inner join 
    products on product_invoice.product_id = products.product_id
inner join 
    invoices on product_invoice.invoice_id = invoices.invoice_id
where 
    month(invoices.invoice_date) = 2
group by 
    products.product_name

该查询返回如下结果:

product_name  | total_amount
--------------+--------------
      chairs  |  70              
      ladders | 500  
      tables  | 150  

如何从中得到:ladders 500


阅读 254

收藏
2021-07-01

共1个答案

admin

Select product_name,max(total_amount) from(
select
products.product_name,
sum(product_invoice.product_amount) as total_amount
from product_invoice
inner join products
on product_invoice.product_id = products.product_id
inner join invoices
on product_invoice.invoice_id = invoices.invoice_id
where month(invoices.invoice_date) = 2
group by products.product_name
) outputTable

2021-07-01