mysql多表查询练习题
时间: 2023-06-19 08:08:48 浏览: 68
好的,以下是一些MySQL多表查询的练习题:
1. 查询订单表中所有订单的详细信息,包括订单号、下单时间、客户名、客户电话、商品名、商品单价、商品数量、商品总价,并按照订单号升序排列。
```
SELECT
o.order_id, o.order_time, c.customer_name, c.customer_phone, p.product_name, p.product_price, od.quantity, od.total_price
FROM
orders o
JOIN
customers c ON o.customer_id = c.customer_id
JOIN
order_details od ON o.order_id = od.order_id
JOIN
products p ON p.product_id = od.product_id
ORDER BY
o.order_id ASC;
```
2. 查询所有客户的订单总数和订单总金额,并按照订单总金额降序排列。
```
SELECT
c.customer_name, COUNT(o.order_id) AS order_count, SUM(od.total_price) AS total_amount
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
LEFT JOIN
order_details od ON o.order_id = od.order_id
GROUP BY
c.customer_name
ORDER BY
total_amount DESC;
```
3. 查询订单表中所有订单的商品种类数和商品总数量,并按照订单总数量降序排列。
```
SELECT
o.order_id, COUNT(DISTINCT od.product_id) AS product_count, SUM(od.quantity) AS total_quantity
FROM
orders o
JOIN
order_details od ON o.order_id = od.order_id
GROUP BY
o.order_id
ORDER BY
total_quantity DESC;
```
希望这些练习题对你有所帮助!