英文:
MYSQL select data from two tables with conditions
问题
以下是翻译好的部分：
Orders Table（订单表）
| Order_ID | Sales_type |
|---|---|
| 10 | Normal |
| 12 | Promo |
| 13 | Normal |
| 14 | Promo |
| 15 | Normal |
| 16 | Promo |
Orders_Products Table（订单产品表）
| Order_Product_ID | Order_ID | Normal_Price | Promotion_Price |
|---|---|---|---|
| 100 | 10 | 50 | 25 |
| 101 | 12 | 100 | 80 |
| 102 | 12 | 60 | 50 |
| 103 | 13 | 40 | 20 |
| 104 | 14 | 50 | 25 |
| 105 | 15 | 40 | 30 |
| 106 | 15 | 300 | 250 |
| 107 | 16 | 150 | 100 |
| 108 | 16 | 75 | 60 |
Expected result（期望结果）:
| Number_Of_Order | Total_Sales_Amount |
|---|---|
| 从订单表中获取的Order_ID的总数，为6 | 根据Sales_type价格从Orders_Products表中获取的总销售额 |
英文:
So I basically got a task which is to create a single query to retrieve the total order as Number_Of_Order and total sales of all orders as Total_Sales_Amount. My main problem would be in creating the conditions for Normal and Promotion.
Orders Table
| Order_ID | Sales_type |
|---|---|
| 10 | Normal |
| 12 | Promo |
| 13 | Normal |
| 14 | Promo |
| 15 | Normal |
| 16 | Promo |
Orders_Products Table
| Order_Product_ID | Order_ID | Normal_Price | Promotion_Price |
|---|---|---|---|
| 100 | 10 | 50 | 25 |
| 101 | 12 | 100 | 80 |
| 102 | 12 | 60 | 50 |
| 103 | 13 | 40 | 20 |
| 104 | 14 | 50 | 25 |
| 105 | 15 | 40 | 30 |
| 106 | 15 | 300 | 250 |
| 107 | 16 | 150 | 100 |
| 108 | 16 | 75 | 60 |
Expected result :
| Number_Of_Order | Total_Sales_Amount |
|---|---|
| Total number of Order_ID from Order table 6 | total sales from Orders_Products table according to Sales_type price |
答案1
得分: 1
使用连接和CASE表达式来使用正确的价格。
SELECT COUNT(DISTINCT o.Order_ID) AS 订单数量,
SUM(CASE WHEN o.Sales_Type = '正常'
THEN op.正常价格 ELSE op.促销价格 END) AS 总销售金额
FROM Orders o
LEFT JOIN Orders_Products op
ON op.Order_ID = o.Order_ID;
英文:
Use a join along with a CASE expression to use the correct price.
<!-- language: sql -->
SELECT COUNT(DISTINCT o.Order_ID) AS Number_Of_Order,
SUM(CASE WHEN o.Sales_Type = 'Normal'
THEN op.Normal_Price ELSE op.Promotion_Price END) AS Total_Sales_Amount
FROM Orders o
LEFT JOIN Orders_Products op
ON op.Order_ID = o.Order_ID;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论