SQL进阶练习一

最新推荐文章于 2024-07-31 15:36:36 发布

YouriL

最新推荐文章于 2024-07-31 15:36:36 发布

阅读量194

点赞数

文章标签： mysql sql 数据库统计学

本文链接：https://blog.csdn.net/YouriL/article/details/105017664

版权

SQL进阶练习一

**题目一
在这里插入图片描述

select s.sname from s join sc on s.son=sc.son 
join c on sc.sno=c.sno where c.cteacher <>"小易"

select sname, avg(score) from s join sc on s.sno=sc.sno 
where count(case when sc.score>90 then 1 else 0 end)>=3

题目二：已知表名A，字段分别为dt（交易日期，格式为20180101）、seller_id（卖家id）、buyer_id（买家id），item_id（商品），amt（交易金额，单位元）：写出下面sql语句: 统计201807~201809期间，卖家id：123，店铺内交易金额最高的商品id以及购买该商品id金额最高的买家id

select item_id, buyer_id from (select Top 1 * from a where item_id = (select item_id from 
(select Top 1 * from A where dt between 20180701 and 20180930 and seller_id=123 
order by amt DESC)) order by amt DESC)

在这里插入图片描述

select t1.date, t1.visits from 
(select id, date, visits from Tourists) as t1 join on 
(select id, date, visits from Tourists where
datediff(day,t1.date,date)=1 ) as t2 join on
(select id, date, visits from Tourists where
datediff(day,t2.date,date)=1) as t3 
where t1.visits>100 and t2.visits>100 and t3.visits>100

在这里插入图片描述

select Top 1 user_id, count(user_id), sum(amt), '天数最多' as note from 
table_t001 group by user_id order by count(user_id) desc union all
select Top 1 user_id, count(user_id), sum(amt), '购买金额最多' as note from 
table_t001 group by user_id order by sum(amt) desc