從產品到訂單的總價格

我想以從產品表中獲得的總價格訂購我的訂單。并用它顯示客戶的詳細資訊。這就是我的表格的樣子：

訂單表

訂購產品表

產品表

客戶表

這是我嘗試過的 SQL

SELECT * 
FROM orders 
LEFT JOIN orders_products USING (order_id) 
LEFT JOIN products USING (product_id) 
LEFT JOIN customers USING (customer_id) 
WHERE order_id = 20001 
ORDER BY order_id DESC

我得到的結果是這個和我想要的我也在片段中描述

#customers {
  font-family: Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 100%;
}

#customers td, #customers th {
  border: 1px solid #ddd;
  padding: 8px;
}

#customers tr:nth-child(even){background-color: #f2f2f2;}

#customers tr:hover {background-color: #ddd;}

#customers th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}

<!--- CSS BY W3schools -->
<h3>Result i get</h3>
<table id="customers">
  <tr>
    <th>Order id</th>
    <th>Date placed</th>
    <th>Customer</th>
    <th>Total price</th>
    <th>Status</th>
  </tr>
  <tr>
    <td>20001</td>
    <td>2022-03-11</td>
    <td>John Doe</td>
    <td>&euro;15</td>
    <td>Unpaid</td>
  </tr>
    <tr>
    <td>20001</td>
    <td>2022-03-11</td>
    <td>John Doe</td>
    <td>&euro;30</td>
    <td>Unpaid</td>
  </tr>
</table>

<h3>Result i want</h3>
<table id="customers">
  <tr>
    <th>Order id</th>
    <th>Date placed</th>
    <th>Customer</th>
    <th>Total price</th>
    <th>Status</th>
  </tr>
  <tr>
    <td>20001</td>
    <td>2022-03-11</td>
    <td>John Doe</td>
    <td>&euro;45</td>
    <td>Unpaid</td>
  </tr>
</table>

因此，具有相同訂單 ID 的訂單是單獨的，我想添加同一訂單的價格并使其成為我已經嘗試過的總價

SELECT * 
FROM orders
LEFT JOIN orders_products USING (order_id) 
LEFT JOIN products sum(price) as total_price USING (product_id) 
LEFT JOIN customers USING (customer_id) 
WHERE order_id > ? 
ORDER BY order_id DESC

但沒有成功

uj5u.com熱心網友回復：

SELECT customers.customer_id, customers.first_name, 
       customers.last_name, customers.email,
       orders.order_id, orders.date_order_placed,
       SUM(products.price * orders_products.quantity) total_order_sum
FROM orders
LEFT JOIN orders_products USING (order_id) 
LEFT JOIN products USING (product_id) 
LEFT JOIN customers USING (customer_id) 
/*
    WHERE orders.order_id = ?
or
    WHERE orders.order_id IN (?, ?, ...)
and also maybe
      AND customers.customer_id = ?
*/
GROUP BY customers.customer_id, customers.first_name, 
         customers.last_name, customers.email,
         orders.order_id, orders.date_order_placed
ORDER BY order_id DESC

uj5u.com熱心網友回復：

你需要SUM在products.price*order_products.quantity和GROUP BYonorder_products.order_id

SELECT
    SUM(products.price * order_products.quantity) AS total_order_price,
    orders.*,
    customers.*
FROM
    orders
    LEFT JOIN order_products ON orders.order_id = order_products.order_id
    LEFT JOIN products ON order_products.product_id = products.product_id
    LEFT JOIN customers USING orders.customer_id = customers.customer_id
WHERE
    order_id = 20001 
GROUP BY
    order_products.order_id 
ORDER BY
    order_id DESC

閱讀有關 GROUP BY 修飾符的更多資訊： https ://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

uj5u.com熱心網友回復：

SELECT 
    order_id,
    date_order_placed,
    CONCAT(first_name, ' ', last_name) AS Customer,
    SUM(price) AS Total,
    status
FROM
    order_table
        LEFT JOIN
    o_product_table USING (order_id)
        LEFT JOIN
    product_table USING (product_id)
        LEFT JOIN
    c_table USING (customer_id)
WHERE
    order_id = 20001
ORDER BY order_id DESC;

標籤：mysql sql

上一篇：Mysql連接多個表而沒有子查詢方法

下一篇：sql-選擇具有非唯一鍵的多個匹配行之一