念_长征

分类

/ 注册

全部文章（共166篇）

题解 | #返回订单数量总和不小于100的所有订单的订单号#

select order_num from OrderItems group by order_num having sum(quantity)>=100 order by order_num;

2023-12-20

0 162

题解 | #每个供应商成本最低的产品#

select vend_id, min(prod_price) cheapest_item from Products group by vend_id order by cheapest_item;

2023-12-20

0 151

题解 | #返回每个订单号各有多少行数#

select order_num, count(*) order_lines from OrderItems group by order_num order by order_lines;

2023-12-20

0 173

题解 | #检索产品名称和描述（二）#

select * from Products where not (prod_desc like '%toy%') order by prod_name;

2023-12-20

0 167

题解 | #查找山东大学或者性别为男生的信息#

# 由于要求不去重，故使用union all关键字 select device_id, gender, age, gpa from user_profile where university='山东大学' union all select device_id, gender, age, gpa fr...

2023-12-20

0 164

题解 | #返回每个顾客不同订单的总金额#

/*使用相关子查询*/ # 查询某个顾客的所有订单的订单总金额 select sum(item_price*quantity) from OrderItems oi join Orders o on oi.order_num=o.order_num where cust_id=cust_id_nu...

2023-12-20

0 129

题解 | #确定哪些订单购买了指定产品（一）#

# 查询购买了BR01产品的订单 select order_num from OrderItems where prod_id = 'BR01' # 查询购买了该产品的订单所对应的顾客ID和订单日期 select cust_id, order_date from Orders where order...

2023-12-20

0 152

题解 | #返回购买价格为 10 美元或以上产品的顾客列表#

# 查询购买价格>=10的订单 select order_num from OrderItems where item_price>=10 # 查询该订单的顾客号 select cust_id from Orders where order_num in( select orde...

2023-12-20

0 150

题解 | #截取出年龄#

# 查询所有字段+提取年龄列 select *, substring_index(substring_index(profile,',',3),',',-1) age from user_submit # 返回每个年龄的用户数 select age, count(*) number from( ...

2023-12-19

0 149

题解 | #找出每个学校GPA最低的同学#

窗口函数与分组函数的区别就是，窗口函数能生成一个新列填入根据每行记录生成的判断值 # 建立使用了窗口函数的子查询 select *, rank() over(partition by university order by gpa) rank_index from user_profile # 返...

2023-12-19

0 160