小应的每天进步史-CSDN博客

原创 585. 2016年的投资

# Write your MySQL query statement belowSELECT round(SUM(insurance.TIV_2016),2) AS TIV_2016FROM insuranceWHERE insurance.TIV_2015 IN ( SELECT TIV_2015 FROM insurance GROUP BY TIV_2015 HAVING COUNT.

2021-12-07 01:03:22 138

select dept_name , count(student_id) as student_number from department as A left outer join student as B on A.dept_id = B.dept_id group by A.dept_nameorder by student_number desc , dept_name一所大学有 2 个数据表，分别是student和department，这两个表保存着每个专业的学生数据...

2021-12-07 00:52:27 2160

原创 578. 查询回答率最高的问题

select A.question_id as survey_logfrom(select question_id , count(*) as answer_countfrom SurveyLogwhere action ='answer'group by question_id) as A # 先子表出来answer_countjoin (select question_id, count(*) as show_countfrom SurveyLogwhere action = '.

2021-12-07 00:43:52 209

原创 570. 至少有5名直接下属的经理

# Write your MySQL query statement belowSELECT NameFROM Employee AS t1 JOIN (SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(ManagerId) >= 5) AS t2 ON t1.Id = t2.ManagerId;Employee 表包含所有员工.

2021-12-05 08:11:05 101

原创 574. 当选者

select name from( select CandidateId from Votegroup by CandidateIdorder by count(*) Desc # 这里的vote表里是id 代表选民的id， candidateID 是和canditdate里面的id 是对应的limit 0,1) a join Candidate b on a.CandidateId = b.id表: Candidate+-----+---------+| id | Name ...

2021-12-02 02:48:15 121

原创 550. 游戏玩法分析 IV

SELECT ROUND( ( SELECT COUNT(DISTINCT a.player_id) FROM Activity a inner join ( select player_id, min(event_date) as first_day from Activity group by player_id ) as tm.

2021-12-02 02:47:24 88

原创 534. 游戏玩法分析 III

select a.player_id,b.event_date,sum(a.games_played) as games_played_so_farfrom Activity ainner join Activity b on a.player_id=b.player_id and a.event_date<=b.event_dategroup by b.player_id,b.event_date;Table:Activity+--------------+---------+|..

2021-12-02 02:46:33 85

原创 1303. 求团队人数

select employee_id , count(employee_id) over (partition by team_id) as team_size from employee员工表：Employee+---------------+---------+| Column Name | Type |+---------------+---------+| employee_id | int || team_id | in...

2021-12-02 01:20:17 119

原创 1294. 不同国家的天气类型

select A.country_name,(case when avg(b.weather_state)<=15 then"Cold"when avg(b.weather_state)>=25 then"Hot"else "Warm" end) as weather_typefrom countries as A , weather as B where a.country_id = b.country_id and b.day between '2019-11-01' and.

2021-12-02 01:05:46 99

原创 1280. 学生们参加各科测试的次数

# Write your MySQL query statement belowselect A.student_id AS 'student_id' ,A.student_name AS 'student_name',B.subject_name AS 'subject_name', count(C.subject_name) as 'attended_exams' from Students as A join Subjects as B left outer join Examinati.

2021-12-02 00:42:23 76

原创 1251. 平均售价

# Write your MySQL query statement belowselect product_id, round(sum(sales)/sum(units),2) as average_pricefrom(select A.product_id , A.price * B.units as sales ,B.unitsfrom Prices as A join UnitsSold as B on A.product_id =B.product_id where B.purc.

2021-12-02 00:01:59 114

原创 1211. 查询结果的质量和占比

# Write your MySQL query statement belowselect query_name, round(AVG (rating/position), 2) as quality, round(sum(if (rating< 3,1 , 0))*100/ count(*), 2) as poor_query_percentagefrom Queriesgroup by query_name查询表 Queries：+------...

2021-12-02 00:00:44 108

原创 sql 函数 if 和 ifnull

if函数：if(a,b,c)if判断，如果a满足条件，返回b，否则返回c例子：select sid, count(if(score>=60,sid,null)) from SC group by sid;运行代码满足及格条件的返回学号sid，不满足条件的返回nullifnull函数IFNULL(expression_1,expression_2);如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返

2021-12-01 22:08:20 1012

原创 case when sql

简单CASE WHEN函数：CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' ENDCASE SCORE WHEN 'B' THEN '良' ELSE '不及格' ENDCASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END等同于，使用CASE WHEN条件表达式函数实现：CASE WHEN SCORE = 'A' THEN '优' WHEN SCORE = 'B' THEN '良' WHEN SCORE

2021-12-01 15:42:16 386

原创 1179. 重新格式化部门表

select id, sum(case month when 'Jan' then revenue end) as Jan_Revenue, sum(case month when 'Feb' then revenue end) as Feb_Revenue, sum(case month when 'Mar' then revenue end) as Mar_Revenue, sum(case month when 'Apr' then revenue end) as A.

2021-12-01 15:39:38 65

原创 1173. 即时食物配送 I

# Write your MySQL query statement belowselect round((select count(*) from Delivery where order_date=customer_pref_delivery_date) / (select count(*) from Delivery )*100,2)as immediate_percentage配送表: Delivery+-----------------------------+---.

2021-12-01 15:32:49 85

原创 1142. 过去30天的用户活动 II

select ifnull(round(count(distinct session_id)/count(distinct user_id),2),0) as average_sessions_per_userfrom Activitywhere datediff("2019-07-27",activity_date) <30Table: Activity+---------------+---------+| Column Name | Type |+----------...

2021-12-01 15:31:57 91

原创 1141. 查询近30天活跃用户数

select activity_date as day, count(distinct(user_id)) as active_usersfrom Activitywhere datediff('2019-07-27',activity_date)<30group by activity_date活动记录表：Activity+---------------+---------+| Column Name | Type |+---------------+---------...

2021-12-01 15:31:08 163

原创 1113. 报告的记录

# Write your MySQL query statement belowselect B.buyer_idfrom Product as Ajoin Sales as B on A.product_id = B.product_idwhere A.product_name ="S8"and B.buyer_id not in( select B.buyer_id from Product as Ajoin Sales as B on A.product.

2021-12-01 15:28:14 101

原创 datediff() 函数

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDateDiffDate 1 SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDateDiffDate -1

2021-12-01 02:35:23 277

原创 1084. 销售分析III

# Write your MySQL query statement belowselect A.product_id, A.product_namefrom Product as A join Sales as B on A.product_id =B.product_id group by product_idhaving min(sale_date) >="2019-01-01"and max(sale_date) <="2019-03-31"Table:Pr...

2021-12-01 02:15:23 50

原创 1083. 销售分析 II

# Write your MySQL query statement belowselect B.buyer_idfrom Product as Ajoin Sales as B on A.product_id = B.product_idwhere A.product_name ="S8"and B.buyer_id not in( select B.buyer_id from Product as Ajoin Sales as B on A.product.

2021-12-01 02:13:08 66

原创 1082. 销售分析 I

# Write your MySQL query statement belowselect seller_idfrom salesgroup by seller_idhaving sum(price)=(select sum(price)from Salesgroup by seller_id order by sum(price) desclimit 0,1)产品表：Product+--------------+---------+| Column Name | T..

2021-11-30 18:01:08 90

原创 1076. 项目员工II

# Write your MySQL query statement belowselect project_idfrom(select *, rank() over ( order by count(employee_id) desc) as rankingfrom projectgroup by project_id) as Awhere A.ranking=1Table:Project+-------------+-------..

2021-11-30 17:59:59 72

原创 1075. 项目员工 I

# Write your MySQL query statement belowselect A.project_id, round(avg(B.experience_years),2) as average_yearsfrom Project as A join Employee as B on A.employee_id =B.employee_id group by project_id项目表Project：+-------------+---------+| Column Na...

2021-11-30 17:56:24 63

原创 1069. 产品销售分析 II

# Write your MySQL query statement belowselect product_id , sum(quantity) as total_quantityfrom Salesgroup by product_id销售表：Sales+-------------+-------+| Column Name | Type |+-------------+-------+| sale_id | int || product_id | int |...

2021-11-30 17:54:38 79

原创 1068. 产品销售分析 I

# Write your MySQL query statement belowselect B.product_name, A.year, A.pricefrom Sales as A join Product as B on A.product_id =B.product_id销售表Sales：+-------------+-------+| Column Name | Type |+-------------+-------+| sale_id | int |...

2021-11-30 17:50:58 91

原创 1050. 合作过至少三次的演员和导演

# Write your MySQL query statement belowselect actor_id,director_idfrom ActorDirectorgroup by actor_id, director_idhaving count(*) >=3ActorDirector表：+-------------+---------+| Column Name | Type |+-------------+---------+| actor_id ...

2021-11-30 17:47:25 99

原创 619. 只出现一次的最大数字

# Write your MySQL query statement below# 正确SQL为select ifnull((select num from MyNumbersgroup by numhaving count(*) = 1order by num desclimit 0,1), null) numMyNumbers 表：+-------------+------+| Column Name | Type |+-------------+------+| nu.

2021-11-30 17:46:31 51

原创 613. 直线上的最近距离

# Write your MySQL query statement belowselect min(abs(A.x-B.x)) as shortestfrom point as AJOINpoint as bon A.x <> B.x表point保存了一些点在 x 轴上的坐标，这些坐标都是整数。写一个查询语句，找到这些点中最近两个点之间的距离。| x ||-----|| -1 || 0 || 2 |最近距离显然是 '1'...

2021-11-30 17:44:41 70

原创 610. 判断三角形

selectx,y,z,case when x+y>z and x+z>y and y+z>x then'Yes'else 'No'end as trianglefrom triangle一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。然而，这个作业非常繁重，因为有几百组线段需要判断。假设表 triangle保存了所有三条线段的长度 x、y、z ，请你帮 Tim 写一个查询语句，来判断每组 x、y、z 是否可以组成一个三角形？| x ...

2021-11-30 17:41:11 84

原创 607. 销售员

# Write your MySQL query statement belowSELECT s.nameFROM salesperson sWHERE s.sales_id NOT IN (SELECT o.sales_id FROM orders o LEFT JOIN company c ON o.com_id = c.com_id W.

2021-11-30 17:39:20 57

原创 603. 连续空余座位

# Write your MySQL query statement belowSelecselect distinct a.seat_idfrom cinema as a, cinema as bwhere abs(a.seat_id-b.seat_id)=1and a.free=1and b.free=1order by seat_id几个朋友来到电影院的售票处，准备预约连续空余座位。你能利用表cinema，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id...

2021-11-29 20:30:19 166

原创 597. 好友申请 I：总体通过率

selectround( ifnull( (select count(*) from (select distinct requester_id, accepter_id from RequestAccepted) as A) / (select count(*) from (select distinct sender_id, send_to_id from FriendRequest) as B), 0), 2) as accept_rate;在 Fac.

2021-11-29 20:29:27 69

原创 586. 订单最多的客户

SELECT customer_numberFROM ordersGROUP BY customer_numberORDER BY COUNT(*) DESCLIMIT 1在表orders中找到订单数最多客户对应的customer_number。数据保证订单数最多的顾客恰好只有一位。表orders 定义如下：| Column | Type ||-------------------|-----------|| orde...

2021-11-29 20:28:31 77

原创 584. 寻找用户推荐人

# Write your MySQL query statement belowselect namefrom customerwhere referee_id <> 2 or referee_id is null给定表customer，里面保存了所有客户信息和他们的推荐人。+------+------+-----------+| id | name | referee_id|+------+------+-----------+| 1 | Will | ...

2021-11-29 20:27:30 76

原创 577. 员工奖金

# Write your MySQL query statement belowselect A.name, B.bonusfrom Employee as A left joinBonus as Bon A.empId=B.empIdwhere bonus <1000 or bonus is null选出所有 bonus < 1000 的员工的 name 及其 bonus。Employee 表单+-------+--------+-----------+-------.

2021-11-29 20:26:34 62

原创 512. 游戏玩法分析 II

# Write your MySQL query statement belowselect player_id, device_idfrom Activitywhere (player_id, event_date)in (select player_id, min(event_date)from Activitygroup by player_id)Table:Activity+--------------+---------+| Column Name | Type ...

2021-11-29 20:25:21 86

原创 511. 游戏玩法分析 I

# Write your MySQL query statement belowselect player_id,min(event_date) as first_loginfrom Activitygroup by player_id活动表Activity：+--------------+---------+| Column Name | Type |+--------------+---------+| player_id | int || devi...

2021-11-29 20:21:23 61

原创 SQL中where与having

where和having的执行顺序where 早于 group by 早于 havingwhere子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句在聚合后对组记录进行筛选where不能使用聚合函数、having中可以使用聚合函数...

2021-11-29 19:49:45 597

空空如也

空空如也