- 博客(8)
- 收藏
- 关注
RSS订阅原创 顺丰2109
一、select userid, sum(if(class='文件', count, 0)) as paper, sum(if(class='娱乐', count, 0)) as entertainment, sum(if(class='', count, 0)) as food, max(count) as most_classfrom( select u...
2019-08-29 16:09:47
224
原创 leetcode 601体育馆的人流量
select id, visit_date, peoplefrom( select a.*, @j:=if(@num2>=100 and people>=100, @j, @j+1) as orders, @num2:=people as peo from stadium as a, (select @num2:=0, @j:=0) as b) as awhe...
2019-08-26 17:34:54
174
原创 leetcode 262行程和用户
##方法一select Department, Employee, Salaryfrom( select a.*, @rank:=case when @id=Department and @money<>Salary then @rank+1 when @id=Department and @money=Salary then ...
2019-08-26 16:37:25
118
原创 leetcode 579查询员工的累积薪水
Employee表中有一年的员工薪资信息，写一个sql来获得3个月内员工工资的累计总和，但不包括最近一个月方法一，待验证select a.Id,a.Month, sum(Salary) as cu_salaryfrom Employee as ajoin Employee as b on a.Id=b.Id and a.Month<=b.Monthjoin( select Id...
2019-08-20 16:01:43
694
3
原创 leetcode 185部门工资前三高的所有员工
select b.name as Department, a.name as Employee, a.Salaryfrom( select a.*, @rank:=case when @department=DepartmentId and @salary<>salary then @rank+1 when @department...
2019-08-19 23:59:37
88
原创 mysql求众数
求每个部门工资的众数思路：先求出每个部门每个工资水平的数量，然后求每个部门每个工资数量的最大值，关联，关联条件是同部门工资出现的数量等于工资出现次数的最大值select a.DepartmentId, a.salaryfrom( select DepartmentId, salary, count(*) as count from Employee group by...
2019-08-19 22:06:00
1356
原创 提取gmv前50%店铺信息
从表purchase中提取五月和六月，总gmv中，两个月分别贡献前50%的店铺表purchase字段说明：ID（店铺ID），name(店铺名)，dt(日期，格式:‘2019-08-25’)，gmvselect a.mon, a.namefrom(select a., @gmv_m:=if(date_format(dt, ‘%Y%m’)=@mon, @gmv_m+gmv, gmv) as g...
2019-08-19 20:36:54
455
原创 leetcode 569员工薪水中位数
###先对每个公司员工工资排序并增加一列序号，然后计算每个公司人数，把两部分join起来取每个公司序号为该公司（总数+1）/2的数据select a.Id, a.Company, a.Salaryfrom(selecta.,@num:=if(@company=Company, @num+1, 1) as num,@company:=CompanyfromEmployee as a,...
2019-08-19 20:16:13
494
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人