- 查询出最近 3 天内所有订单的金额总计。
SELECT SUM(amount) AS total_amount
FROM orders
WHERE order_date BETWEEN CURRENT_DATE - 3 AND CURRENT_DATE;
- 查询出所有订单中,订单金额大于 1000 元的订单数量。
SELECT COUNT(*) AS order_count
FROM orders
WHERE amount > 1000;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单。
SELECT *
FROM orders
WHERE amount BETWEEN 1000 AND 2000;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单金额降序排列。
SELECT *
FROM orders
WHERE amount BETWEEN 1000 AND 2000
ORDER BY amount DESC;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单日期升序排列,如果订单日期相同,则按照订单金额升序排列。
SELECT *
FROM orders
WHERE amount BETWEEN 1000 AND 2000
ORDER BY order_date, amount;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单日期升序排列,如果订单日期相同,则按照订单金额降序排列。
SELECT *
FROM orders
WHERE amount BETWEEN 1000 AND 2000
ORDER BY order_date, amount DESC;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单日期分组,统计每个日期的订单数量。
SELECT order_date, COUNT(*) AS order_count
FROM orders
WHERE amount BETWEEN 1000 AND 2000
GROUP BY order_date;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单日期分组,统计每个日期的订单金额总计。
SELECT order_date, SUM(amount) AS total_amount
FROM orders
WHERE amount BETWEEN 1000 AND 2000
GROUP BY order_date;
- 查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单日期分组,统计每个日期的订单金额总计,如果订单金额总计超过 10000 元,则将订单金额总计标记为红色,并将订单金额总计按降序排列。
SELECT order_date, SUM(amount) AS total_amount,
CASE
WHEN SUM(amount) > 10000 THEN 'red'
ELSE 'black'
END AS color
FROM orders
WHERE amount BETWEEN 1000 AND 2000
GROUP BY order_date
ORDER BY total_amount DESC;
10.查询出所有订单中,订单金额在 1000 元至 2000 元之间的订单,并按照订单日期和订单商品分组,统计每个日期每个商品的订单数量。
SELECT order_date, product_id, COUNT(*) AS order_count
FROM orders
WHERE amount BETWEEN 1000 AND 2000
GROUP BY order_date, product_id;