英文:
I don't know How to do this query , I only could find the volume of carton 10
问题
Here's the translated code portion:
选择 (c.len*c.width*c.height) 作为 最大体积
从 carton c
其中 c.carton_id = 10;
英文:
Product table
| ## Product_id ## | ## Len ## | ## Width ## | ## Height ## |
|---|---|---|---|
| 239 | 350 | 300 | 100 |
| 240 | 80 | 80 | 150 |
This is a sample product table , I have given you the relevant columns.This actual table has 60 rows of information like this.
Order_items table
| ## Order_id ## | ## Product_id ## | ## Product_quantity## |
|---|---|---|
| 10001 | 237 | 1 |
| 10002 | 240 | 3 |
| 10003 | 247 | 4 |
This is a sample order_items table where product quantity is no of items sold for that particular order_id.
Carton table
| ## Carton_id ## | ## Len ## | ## Width ## | ## Height ## |
|---|---|---|---|
| 10 | 600 | 300 | 100 |
| 20 | 80 | 80 | 150 |
The carton_id 10 has the actual values of length, width and height.
The query is:
Write a query to display order_id and volume of the biggest order (in terms of volume) that can fit in carton id 10 . Expected 1 row in final output.
I am not able to do more than this, it's too confusing. Please help
select (c.len*c.width*c.height) as Max_volume
from carton c
where c.carton_id = 10;
答案1
得分: 0
如果每个订单中都只有一个产品，就像您的示例数据中所示，那么一种方法是进行连接和筛选。
select o.order_id, p.len * p.width * p.height volume
from order_items o
inner join products p on p.product_id = o.product_id
inner join cartons c on c.len >= p.len and c.width >= p.width and c.height >= p.height
where c.carton_id = 10
order by volume desc limit 1
这个查询会筛选出适合在10号箱子中的产品订单（意味着产品的三个尺寸小于箱子的尺寸），然后按降序排列，仅保留第一行。
英文:
If there always is a single product in each order, as shown in your sample data, then one option is to join and filter.
select o.order_id, p.len * p.width * p.height volume
from order_items o
inner join products p on p.product_id = o.product_id
inner join cartons c on c.len >= p.len and c.width >= p.width and c.height >= p.height
where c.carton_id = 10
order by volume desc limit 1
The query filters orders on products that fit in a carton 10 (meaning that the three dimensions of the product are smaller than those of the carton), then sorts by descending volume and retains the first row only.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论