- 点击上方“中国统计网”设置⭐星标不迷路!-
根据下面的例表编写SQL查询语句: 表名:order 表名:product 表名:store 1.查找符合下列要求的产品,并按照产品价格降序排列:CategoryA 且颜色为yellow,或者Weight大于5; 参考代码select *
from product
where Category = 'CategoryA'
and (Color = 'Yellow' OR weight > 5)
order by Price desc
2.请计算每一位客人的总购买金额(Amount),总购买订单数,总购买产品件数(Quantity),同一个客人同一天的订单算作一单,并筛选出总购买金额大于等于800的客人,按金额降序排列;
参考代码
SELECT NAME
,sum(amount) as sum_amt
,count(DISTINCT orderdate) as sum_order
,sum(quantity) as 总商品数
FROM `order`
GROUP BY NAME
having sum(amount) >= 800
order by sum_amt DESC;
3.请查找每个城市(City)购买金额排名第二的客人,列出其购买城市,姓名,和购买金额;
参考代码
SELECT *
FROM
(
SELECT city
,`NAME`
,sum(amount) as amt_total
,row_number()over(PARTITION by city order by sum(amount) desc) as ranking
FROM `order` as a
inner join store as b ON a.store = b.store
GROUP BY city,`NAME`) t
WHERE ranking = 2;
4.购买过ProductA 且 购买过 ProductB的顾客人数;
参考代码
法一:借助count
SELECT count(*) as cust_num
FROM (SELECT Name
,count(DISTINCT product) as scount
from `order`
WHERE product = 'ProductA' or product = 'ProductB'
group by Name
having scount > 1) t
法二:嵌套子查询
select name from `order`
where product = 'ProductA'
and name in (select name from `order`
where product = 'ProductB');
法三:借助 join
SELECT a.name
from
(select name from `order` where product = 'ProductA') a
inner join
(select name from `order` where product = 'ProductB') b
on a.name = b.name;
测试题4常见错误
错例①:“in” 为逻辑"或"的关系
# 形式①
(select `name` from `order`
where product = 'ProductA'
or product = 'ProductB')
# 形式②
select `name` from `order`
where product in ('ProductA','ProductB')
错例②:使用 in 和 union不能够实现需求
select name from product
where name in (select name from product where product = "productA"
union select name from product where product = "productB" )
错例③:在限制条件中使用 “and” 不能满足需求,且会导致结果为null;
select name from `order`
where `name`in(SELECT DISTINCT name
from product where product = 'Product')
and`name`in(select name
from `order` where product = 'ProductB');
本次考点
考察知识点:join、having、子查询、聚合分组、窗口函数、条件筛选等语句的掌握以及sql技能综合运用的能力
考察同学们的审题是否细致、考查理解业务的能力
将业务需求转换成构建SQL语句、并得到分析结果的能力
需要同学们熟记SQL子句的书写顺序和运行顺序
考察对having语句的掌握,很多人会把聚合函数写到where子句中
End 题目解析:爱数据小辉 数据分析更多有趣、实用的知识关注爱数据学院社区分享
往期小编推荐
点击标题即可阅读??
数据职场丨数据分析师!如何用『反推法』做职业生涯规划?
资源分享丨你要练手的【免费数据源】来了!!!
大咖交流丨集齐“BAT”数据岗位offer背后的那些事……
戳