目录
题目链接
https://www.nowcoder.com/exam/oj/ta?tpId=298
SQL题解汇总
https://blog.csdn.net/guliguliguliguli/article/details/126109166
08 汇总数据
SQL 83 确定已售出产品的总数
考察知识点
- 聚合函数 SUM()
- 别名的使用
答案
注意题目中的示例结果,需要起别名
SELECT SUM(quantity) items_ordered
FROM OrderItems;
SQL 84 确定已售出产品项 BR01 的总数
考察知识点
- 聚合函数 SUM()
- 别名的使用
答案
SELECT SUM(quantity) items_ordered
FROM OrderItems
WHERE PROD_ID = 'BR01';
开发中的建议:
- 当过滤条件中有聚合函数时,此过滤条件必须声明在
HAVING
中 - 当过滤条件中没有聚合函数时,此过滤条件声明在
WHERE
和HAVING
中都可以,但是建议声明在WHERE中
SQL 85 确定 Products 表中价格不超过 10 美元的最贵产品的价格
考察知识点
- 聚合函数 MAX()
- 别名的使用
答案
SELECT MAX(prod_price) max_price
FROM Products
WHERE prod_price <= 10;
09 分组数据
SQL 86 返回每个订单号各有多少行数
考察知识点
- 聚合函数COUNT()
- COUNT(*)
- COUNT(1)
- COUNT(字段)
- GROUP BY()
- SELECT中出现非聚合函数的字段,必须声明在GROUP BY后
- 别名的使用
- 排序 ORDER BY()
- DESC 降序
- ASC 升序
答案
SELECT order_num,COUNT(order_num) order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines ASC;
SQL 87 每个供应商成本最低的产品
考察知识点
- 聚合函数MIN()、GROUP BY
- 别名的使用
- 排序ORDER BY
答案
SELECT vend_id, MIN(prod_price) cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item ASC;
SQL 88 返回订单数量总和不小于100的所有订单的订单号
考察知识点
- GROUP BY … HAVING …
- ORDER BY
- DESC
- ASC
答案
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity)>=100
ORDER BY order_num ASC;
SQL 89 计算总和
考察知识点
- 聚合函数SUM()、GROUP BY … HAVING …
答案
SELECT order_num, SUM(item_price*quantity) total_price
FROM OrderItems
GROUP BY order_num
HAVING SUM(item_price*quantity)>=1000
ORDER BY order_num ASC;
SQL 90 纠错3
考察知识点
- GROUP BY …
- SELECT中出现的非聚合函数字段,必须声明在GROUP BY中
- GROUP BY中声明的字段可以不出现在SELECT中
答案
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;