SQL
九幽孤翎
蜉蝣只有认清自己的渺小,才能有化茧成蝶的一天
展开
-
Leetcode_1107_每日新用户统计_SQL
with t as ( select user_id, min(activity_date) as login_date from traffic where activity = 'login' group by user_id)select login_date, count(distinct user_id) as user_countfrom twhere DateDiff("2019-06-30", login_date) <= 90group b原创 2022-03-01 13:58:20 · 180 阅读 · 0 评论 -
Leetcode_1988_找出每所学校的最低分数要求_SQL
with t as ( select s.school_id, e.score from schools as s left join exam as e on s.capacity >= e.student_count)select school_id, ifnull(min(score), -1) as scorefrom tgroup by school_id原创 2022-03-01 10:33:41 · 170 阅读 · 0 评论 -
Leetcode_580_统计各专业学生人数_SQL
# Write your MySQL query statement belowselect d.dept_name, count(s.student_id) as student_numberfrom department as dleft join student as s on d.dept_id = s.dept_idgroup by d.dept_idorder by student_number desc如果直接写的话,很绕的一道题,个人比较喜欢用下面的写法,感觉更加清晰直观w原创 2022-03-01 09:43:54 · 251 阅读 · 0 评论 -
Leetcode_578_查询回答率最高的问题_SQL
select question_id as survey_logfrom surveyloggroup by question_idorder by sum(action='answer')/sum(action='show') desclimit 1;很有意思的解法,sum中可以带过滤条件也可以用sum(if(action = ‘answer’, 1, 0))with t as ( select question_id, count(question_id) as ques原创 2022-02-16 12:24:08 · 314 阅读 · 0 评论 -
Leetcode_577_员工奖金_SQL
select e.name, b.bonusfrom employee as e left join bonus as b on e.empId = b.empId where b.bonus < 1000 or b.bonus is nullnull需要用is或is not来进行判断原创 2022-02-16 11:53:35 · 371 阅读 · 0 评论 -
Leetcode_574_当选者_SQL
with t as ( select candidateId from vote group by candidateId order by count(*) desc limit 1)select namefrom candidate right join ton id = t.candidateId原创 2022-02-16 11:51:47 · 291 阅读 · 0 评论 -
Leetcode_570_至少有5名直接下属的经理
select e1.namefrom employee as e1where 5 <= ( select count(*) from employee as e2 where e2.managerId = e1.id)原创 2022-02-15 18:33:56 · 833 阅读 · 0 评论 -
Leetcode_569_员工薪水中位数_SQL
with t as ( select id, company, salary, row_number() over(partition by company order by salary) as 'rk', count(id) over(partition by company) as 'cnt' from Employee)select id, company, salaryfrom twhere rk >= cnt / 2 and rk <= cnt / 2 +原创 2022-02-15 18:26:28 · 143 阅读 · 0 评论 -
Leetcode_550_游戏玩法分析4_SQL
select round( ( select count(distinct a.player_id) from Activity as a inner join Activity as b on a.player_id = b.player_id and DateDiff(a.event_date, b.event_date) = 1 and b.event_date = ( select min(event_date)原创 2022-02-15 17:38:36 · 2151 阅读 · 0 评论 -
Leetcode_534_游戏玩法分析3_SQL
select a1.player_id, a1.event_date, sum(a2.games_played) as games_played_so_farfrom activity as a1 join activity as a2 on a1.player_id = a2.player_idwhere a1.event_date >= a2.event_dategroup by a1.player_id, a1.event_dateselect player_id, event_d原创 2022-02-15 16:50:40 · 1859 阅读 · 0 评论 -
Leetcode_512_游戏玩法分析2_SQL
with t as (select player_id, min(event_date) as dfrom activitygroup by player_id)select a.player_id, a.device_idfrom activity as a, twhere a.event_date = t.d and a.player_id = t.player_idselect a1.player_id, a1.device_idfrom activity as a1where原创 2022-02-15 16:34:20 · 1899 阅读 · 0 评论 -
Leetcode_511_游戏玩法分析1_SQL
select player_id, min(event_date) as 'first_login'from activitygroup by player_id原创 2022-02-15 15:17:36 · 1517 阅读 · 0 评论 -
Leetcode_197_上升的温度_SQL
SELECT w2.IdFROM Weather w1, Weather w2WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1AND w1.Temperature < w2.TemperatureDATEDIFF(w2.RecordDate, w1.RecordDate):前-后的天数差原创 2022-02-15 15:15:41 · 253 阅读 · 0 评论 -
Leetcode_196_删除重复的电子邮箱_SQL
with t as ( select p1.id as pid from person as p1, person as p2 where p1.email = p2.email and p1.id > p2.id)delete person from person, twhere person.id = t.pid原创 2022-02-15 15:13:01 · 200 阅读 · 0 评论 -
Leetcode_1179_重新格式化部门表_SQL
MySQL 的 case when 的语法有两种:简单函数CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END枚举这个字段所有可能的值*搜索函数CASE WHEN [expr] THEN [result1]…ELSE [default] END搜索函数可以写判断,并且搜索函数只会返回第一个符合条件的值,其他case被忽略select id, sum(case month when 'Jan' then reve原创 2022-02-14 14:07:22 · 95 阅读 · 0 评论 -
Leetcode_185_部门工资前三高的所有员工_SQL
with t as ( select id, name as 'Employee', Salary, DepartmentId, dense_rank() over(partition by DepartmentId order by salary desc) as 'rank' from employee)select d.name as 'Department',t.Employee, t.Salaryfrom tleft join Department as d on t.原创 2022-02-14 10:12:59 · 112 阅读 · 0 评论 -
Leetcode_184_部门工资最高的员工_SQL
select d.name as 'department',e.name as 'employee', salaryfrom employee as e join department as dwhere e.departmentId = d.id and (e.departmentId, e.salary) in (select e.departmentId, max(e.salary) as sfrom employee as e, department as dgroup by e.depa原创 2022-02-13 14:51:02 · 74 阅读 · 0 评论 -
Leetcode_183_从不订购的客户_SQL
select name as 'customers'from customerswhere id not in ( select customerid from orders)原创 2022-02-13 14:47:36 · 88 阅读 · 0 评论 -
Leetcode_182_查找重复的电子邮箱_SQL
select emailfrom persongroup by emailhaving count(email) > 1# Write your MySQL query statement belowselect emailfrom ( select email, count(email) as cnt from person group by email) as twhere cnt > 1原创 2022-02-13 14:46:31 · 580 阅读 · 0 评论 -
Leetcode_181_超过经理收入的员工_SQL
select a.name as Employeefrom employee as a, employee as bwhere a.salary > b.salary and a.managerId = b.id原创 2022-02-13 14:43:50 · 96 阅读 · 0 评论 -
Leetcode_180_连续出现的数字_SQL
select distinct l1.num as consecutivenums from logs l1,logs l2,logs l3where l1.id = l2.id - 1and l2.id = l3.id - 1and l1.num = l2.numand l2.num = l3.num三表联查就完事了原创 2022-02-13 14:39:24 · 153 阅读 · 0 评论 -
Leetcode_178_分数排名_SQL
# Write your MySQL query statement belowselect score, dense_rank() over(order by score desc) as `Rank`from scores同分同名 dense_rank()原创 2022-02-13 14:37:49 · 580 阅读 · 0 评论 -
Leetcode_177_第N高的薪水_SQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN set n := n -1; RETURN ( # Write your MySQL query statement below. select salary from employee group by salary order by salary desc limit n, 1 );EN原创 2022-02-13 14:36:58 · 74 阅读 · 0 评论 -
排名问题总结_SQL
排名是数据库中的一个经典题目,实际上又根据排名的具体细节可分为3种场景:连续排名,例如薪水3000、2000、2000、1000排名结果为1-2-3-4,体现同薪不同名,排名类似于编号同薪同名但总排名不连续,例如同样的薪水分布,排名结果为1-2-2-4同薪同名且总排名连续,同样的薪水排名结果为1-2-2-3在mysql8.0中有相关的内置函数,而且考虑了各种排名问题:row_number(): 同薪不同名,相当于行号,例如3000、2000、2000、1000排名后为1、2、3、4rank原创 2022-02-13 14:36:01 · 173 阅读 · 0 评论 -
Leetcode_176_第二高的薪水_SQL
select (select distinct salaryfrom Employeeorder by salary desclimit 1, 1) as SecondHighestSalary如果直接查的话,会返回空l而不是nullselect ifnull(子查询, null) 如果子查询为null, 返回null,否则返回子查询原创 2022-02-13 14:24:58 · 252 阅读 · 0 评论 -
Leetcode_175_组合两个表_SQL
left join是以左表为主select FirstName, LastName, City, statefrom Person left join Addresson Person.PersonId = Address.PersonId原创 2022-02-12 11:01:43 · 187 阅读 · 0 评论 -
Leetcode_1082_销售分析1_SQL
恶补SQLing第一种写法,需要注意的点是allhaving是和group by连用时候的whereIN、ALL、ANY、SOME的解释IN:在范围内的值,只要有就trueALL: 与子查询返回的所有值比较为true 则返回trueANY:与子查询返回的任何值比较为true 则返回trueSOME:是ANY的别称,很少用SELECT seller_idfrom salesgroup by seller_idhaving SUM(price) >= ALL ( sele原创 2022-02-11 22:11:33 · 375 阅读 · 0 评论