寻找用户推荐人
写一个查询语句,返回一个客户列表,列表中客户的推荐人的编号都 不是 2。
方法一:
select name from customer
where referee_id != 2
上述写法只能得到id为5的客户,如果要保留推荐人为NULL的客户,则select name from customer where referee_id != 2 or referee_id is null
方法二:
select name from customer
where ifnull(referee_id,0) != 2;
注意:使用where子句应尽量避免和ifnull()连用,会降低查询效率,可以使用如下语句;
select c2.name from
(
select c1.name,ifnull(referee_id,0) as referee_id from customer c1
) c2
where c2.referee_id != 2;
订单最多的客户
1.订单最多的客户只有一个的情况
如图,想要找订单最多的客户,我们
1.先按照customer_number进行分组
2.在分好的各组中用count()进行数数
3.将得到的数降序排列
4.使用LIMIT子句可以得到最多的(LIMIT子句用来限制返回的行数,由于是最多,我们设置1即可)
2.如果最多的客户存在多个的情况
在第一种情况中,我们已经将订单最多的客户筛选出来了;如果还有并列的,我们只需要对其进行匹配;
1.分组
2.采用having+count()将最多订单的实际数目进行匹配
select O2.customer_number from Order_1 O2
Group by O2.customer_number
having count(O2.customer_number) = (
select count(O1.customer_number) as customer_num from Order_1 O1
Group by O1.customer_number
order by customer_num DESC
LIMIT 1
)
还可以采用窗口函数
dense_rank()##排序函数,排名连续,并列名次记为同一数字
select O1.customer_number from
(
select O.customer_number,dense_rank() over (order by count(O.customer_number) desc) as ranking
from Order_1 O
group by O.customer_number
) O1
where ranking = 1;
报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名
方法一:不采用表外连接
1.首先在表2中找到公司名为RED的com_id
2.在表3找到com_id=1时的sales_id
3.在表1中找到sales_id=1,4的记录
5.选择不在sales_id=1,4的记录(NOT IN)
select name from SalesPerson
where sales_id not in ( select sales_id from Orders
where com_id in (
select com_id from Company
where name = "RED"
))
方法二:采用表外连接
1.首先将表2表3按照条件连接
2.在表1中找到在sales_id=1,4的记录(NOT IN)
select S.name from SalesPerson S
where S.sales_id not in (select O.sales_id from Orders O
left join Company C
on O.com_id = C.com_id
where C.name = "RED")
合作过至少三次的演员和导演
select actor_id,director_id from ActorDirector
group by actor_id,director_id
having count(director_id) >=3
注意group by actor_id,director_id
须得是两个列的分组,只有一个的话是不对的