我有一些数据库表:
Transaction detail_transaction product
2 (id_sale) 1 (id_detail) 3 (id_product)
Yes (status_paidOff) 2 (id_sale) fish rolls (product_name)
2020-05-28(transaction_date) 3 (id_product)
Product (sale_type) 7 (total_sale)
所以输出是这样的:
Product Name
Total Sale
Fish rolls 7
我想在detail_transaction
中选择具有MAXtotal_sale
的行,条件为:
- 它已得到回报 = 是
- 第 5 个月的交易(您可以使用
MONTH(transaction_date)=5
( - 销售类型为
Product
谁能帮我这些?
如果你只想要满足条件的detail_transaction
的顶行,你可以将detail_transaction
连接到transaction
,用where
子句过滤,然后order by
并limit
:
seslect dt.*
from detail_transaction dt
inner join transaction t on t.id_sale = dt.id_sale
where
t.status_paidoff = 'yes'
and month(t.transaction_date) = 5
and t.sale_type = 'Product'
order by dt.total_sale desc
limit 1