【高频SQL基础50题】31-35
又到SQL。
目录
1.查询结果的质量和占比
聚合题。
# Write your MySQL query statement below
SELECT t1.query_name,ROUND((SUM(t1.rating/t1.position))/COUNT(*),2) AS quality,ROUND((SUM(IF(t1.rating<3,1,0))/COUNT(*))*100,2) AS poor_query_percentage
FROM Queries t1
GROUP BY t1.query_name
HAVING query_name is not null;
2.求关注者的数量
排序和分组题。
# Write your MySQL query statement below
SELECT user_id,COUNT(follower_id) followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id asc;
asc递增排序；
3.指定日期的产品价格
窗口题。
t1用来取出商品id，
t2用来更新最新的商品价格，
t3用来包含t2，并判断是否需要更新
t1=t3的id构造出最后的表。
# Write your MySQL query statement below
select t1.product_id,if(t3.price is null,10,t3.price) price
FROM (select distinct product_id from Products) t1
LEFT JOIN (select product_id,price
FROM (select product_id,new_price price,Rank() OVER (PARTITION BY product_id ORDER BY change_date DESC) "r" FROM Products
WHERE change_date<'2019-08-17') t2
WHERE r=1) t3
ON t1.product_id=t3.product_id
4.好友申请 II ：谁有最多的好友
子查询+union all
# Write your MySQL query statement below
SELECT ids as id,COUNT(*) AS num
FROM(
SELECT requester_id AS ids
FROM RequestAccepted
UNION ALL
SELECT accepter_id AS ids
FROM RequestAccepted
) AS t
GROUP BY ids
ORDER BY num DESC
LIMIT 1;
5.按日期分组销售产品
高级字符串函数题。
# Write your MySQL query statement below
select
sell_date,count(distinct product)num_sold,
group_concat(distinct product
order by product
separator ',') products
from
Activities
group by sell_date
order by sell_date 
原文地址：https://blog.csdn.net/m0_73629042/article/details/142864648
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！