22個簡單的SQL題目

本篇文章夾雜著題目和自己的題解，歡迎來交流

1、比賽名單整理

select a.team_name as `隊伍A`,b.team_name as `隊伍B` from competition_list a inner join competion_list b on a.team_name<b.team_name order by a.team_name,b.team_name

上面是課本中的解答，但自己想用cross join來做，歡迎交流

select a.team_name as `隊伍A`,b.team_name as `隊伍B` from competition_list a cross join competition_list b on a.team_name<b.team_name order by a.team_name,b.team_name

2、參與優惠活動的商品
本題注意將時間條件分四段asbe\aseb\saeb\sabe

select commodity_id from product_promotion where (start_date<='2021-01-09' and start_date>='2021-01-07')or(end_date>='2021-01-07' and end_date<='2021-01-09')or(end_date>='2021-01-09' and start_date<='2021-01-07')or(start_date>='2021-01-07' and end_date<='2021-01-09')

3、連續售出的商品
找出連續下單大于等于3次的商品ID

自己的解法

select commodity_id from(select commodity_id,order_id,rank() over (partition by commodity_id order by order_id) as ranking,order_id - rank() over (partition by commodity_id order by order_id) as difffrom sold_succession )b group by commodity_id,b.diff having count(*)>=3

課本的題解，有趣

select distinct commodiyt_id from(select commodity_id,order_id,lag(order_id,2) over(partition by commodity_id order by order_id) as temp,from sold_succession )a where order_id=temp+2

4、奇偶互換位置

select (case when mod(student_id,2)=1 and student_id=(select count(*) from student_info) then student_idwhen mod(student_id,2)=1 then student_id+1else student_id-1 end) as student_id,student_name from student_info order by student_id

5、熱門游戲排名
自己的解答，需要再研究group_concat的用法

select category,group_concat(game,'2') as game from(select category,gamefrom(select *,rank() over (partition by category order by downloads desc) as rankingfrom game_ranking)bwhere b.ranking<=2 )a group by category

課本的做法，妙啊

select category,substring_index(group_concat(game order by downloads desc),',',2) as game from game_ranking group by category

6、商品銷量同環比

select sales_volume,sales_volume/year_on_year as year_ratio,sales_volume/month_on_month as month_ration from(select `month`,lag(sales_volume,1) over(order by 'month') as month_on_month,lag(sales_volume,12) over(order by 'month') as year_on_yearfrom comparative_analysis )b where 'month'='2021-05'

7、社區生鮮APP覆蓋分析

自己的解法

select count(distinct user_id) as num from fresh_food where locate('A',app)>0

題目的解法

select sum(case when find_in_set('A',app)>0 then 1 else 0 end) as num from fresh_food

8、社區團購行為分析
(1)查詢每個用戶首次登錄的渠道名稱

自己的解法

select user_id,login_source from(select *,rank() over(partition by user_id order by login_date) as rankingfrom group_buy )b where b.ranking=1

課本的解法

select a.user_id,a.login_source from group_buy a inner join(select user_id,min(login_date) as first_login_datefrom group_buygroup by user_id )b on a.user_id=b.user_id and a.login_date=b.first_login_date

(2)查詢用戶登錄日期和累計下載量

select user_id,login_date,sum(order_count) over(partition by user_id order by login_date) as total_order_count from group_buy

9、“雙十一”活動的電商GMV分析

select `date`,concat(gmv_rate,'%') as ratio from(select `date`,mall_gmv,(mall_gmv-lag(mall_gmv,7) over (order by 'date')) as gmv_diff,(mall_gmv-lag(mall_gmv,7) over (order by 'date'))/lag(mall_gmv,7) over (order by 'date') *100 as gmv_ratefrom gmv_infowhere 'date' between '2020-11-04' and '2020-11-17' )t where `date` between '2020-11-11' and '2020-11-17'

10、統計字符出現次數

select text_id,length(text_context)-length(replace(text_context,'*','')) as num from original_text

11、網站訪問量分析

select a.visit_year,a.max_visit from(select substring(data_content,1,4) as visit_year,substring(data_content,-4,4) as max_visit,rank() over (partition by substring(data_content,1,4) order by substring(data_content,-4,4) desc) as rankingfrom website_visitorder by data_content )a where a.ranking=1 order by a.visit_year

12、文本記錄連接

select text_id,group_concat(text_content separator '&') as new_text from convert_table group by text_id order by text_id

13、行列互換

select `year`,sum(case when quarter=1 then amount else 0) as `一季度`,sum(case when quarter=2 then amount else 0) as `二季度`,sum(case when quarter=3 then amount else 0) as `三季度`,sum(case when quarter=4 then amount else 0) as `四季度` from purchase_quantity group by `year`

14、尋找符合要求的訂單

select order_id,abs(money-sum(money) over (order by order_id)) as diff from consumer_order order by diff,order_id limit 1

15、用戶購物信息統計

select a.user_id,a.register_date as reg_date,count(b.order_id) as order_2021 from user_register_info a left join user_order_info b on a.user_id=b.user_id where year(order_date)=2021 group by a.user_id,a.register_date

但是有些用戶如果在2021年沒有消費的話，上面的方法則沒法展示出來。這是不符合要求的，因此選用下面的方式

select aa.user_id,aa.register_id,ifnull(bb.order_2021,0) as order_2021 from user_register_info aa left join(select b.user_id,b.register_date,count(order_id) as order_2021from user_order_info aleft join user_register_info bon a.user_id=b.user_idwhere year(order_date)=2021group by b.user_id,b.register_date )bb on aa.user_id=bb.user_id

16、用戶首單消費金額

select user_id,payment from(select user_id,payment,rank() over (partition by user_id order by paytime) as rankingfrom user_order )a where a.ranking=1

17、優惠券使用分析

select a.user_id,count(collection_date) as num from coupon_collection a inner join consumption_info b on a.user_id=b.user_id where datediff(consumption_date,collection_date) between 1 and 7 group by a.user_id

18、游戲玩家登錄情況分析

select user_id,count(`date`) as num from(select user_id,substring(login_time,1,10) as `date`from game_login )a group by user_id,`date` having count(`date`)>1

19、員工績效考核

select employee_id,total_score from(select employee_id,case when target_a>=8 then 1 else 0 end as target_a,case when target_b>=8 then 1 else 0 end as target_b,case when target_c>=8 then 1 else 0 end as target_c,case when target_d>=8 then 1 else 0 end as target_d,case when target_e>=8 then 1 else 0 end as target_e,target_a+target_b+target_c+target_d+target_e as total_scorefrom employee_performance )a where target_a+target_b+target_c+target_d+target_e>=4 order by total_score desc,employee_id

20、找出各類別商品銷量最高的商品

select product_category,product_id,sale from(select *,rank() over (partition by product_category order by sale desc) as rankingfrom product_sale )a where a.ranking=1

21、找出每個部門薪資第二高的員工
題解中用的rank()，但本人認為大概用dense_rank()??歡迎來交流一下

select employee_id,employee_name,employee_salary,department_id from(select *,dense_rank() over (partition by department_id order by employee_salary desc) as rankingfrom employees )a inner join department b on a.department_id=b.department_id where a.ranking=2

22、找出游戲中最活躍的用戶

select user_id,count(*) as cnt from(select request_id as user_idfrom pk_infounion allselect accept_id as user_idfrom pk_info )a group by user_id order by cnt limit 1

總結

以上是生活随笔為你收集整理的《SQL数据分析——从基础破冰到面试题解》题解1的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。