weixin_43329319的博客

1、题目描述Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than 1 month from today. Assume today is 2019-06...

1、题目描述来源：力扣（LeetCode）2、解题思路首先对每个数字出现次数统计select num,count(num) from my_numbers group by num having count(num)=1然后找出最大的数字select max(num) as num3、提交记录select max(num) as numfrom(select num,count...

1、题目描述来源：力扣（LeetCode）2、解题思路其实就是做3个子表分别查询属于各大洲的学生，然后以最多人的大洲为主表，进行外连接1# 3个子表，例如，美洲的select name as America,@a:=@a+1 as rank1from student ,(select @a:=0) awhere continent="America"order by name...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 首先增加子表，查询每月公司的平均工资select date_format(pay_date,'%Y-%m') as pay_m,avg(amount) as com_tfrom salarygroup by pay_m2# 然后增加子表，按部门和月度分类，查询平均工资select date_format(pay_d...

1、题目描述来源：力扣（LeetCode）2、解题思路其实就是统计各人的关注着数量，但是排除掉没有关注过其他人的1# 两表联查，一个记录被谁关注（关注者）f1，另一个记录关注了谁f22# 分类统计数量，记得去重count(distinct f1.followee,f1.follower) as num3# 排除，没有关注其他人的where f2.follower is not nul...

1、题目描述来源：力扣（LeetCode）2、解题思路两表联查，记得让距离计算为正where p1.x>p2.x3、提交记录select min(p1.x-p2.x) as shortestfrom point p1,point p2where p1.x>p2.x...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 两表联查2# 去重，where p1.x<>p2.x or p1.y<>p2.y3# 距离计算公式sqrt(min(power((p1.x-p2.x),2)+power((p1.y-p2.y),2)))，我把取最小值放在根号里面3、提交记录select round(sqrt(min(power((p...

1、题目描述来源：力扣（LeetCode）2、解题思路分类问题，也可以用case when then end3、提交记录select *,if( x+y>z and x+z>y and y+z>x , 'Yes','No') as trianglefrom triangle...

1、题目描述来源：力扣（LeetCode）2、解题思路其实就是分类，用case when then end1# 对所有父节点p_id，进行子节点查询，select p_id,count(id) as s_id from tree group by p_id2# 2表连接，一个作为父节点查询，一个作为子节点查询，条件是t1.id=cou.p_id3# 按数量分类3、提交记录sel...

1、题目描述来源：力扣（LeetCode）2、解题思路思路一1# 首先找出卖给RED的销售2# 然后取不在1#条件内的销售where s2.sales_id not in思路二#1 主要是对于company表的RED进行取反，以salesperson为主表：right join salesperson s#2 条件是c.name='RED'，然后取反where o.order_...

1、题目描述来源：力扣（LeetCode）2、解题思路两表联查，记得去重distinctc2.seat_id3、提交记录select distinct c2.seat_idfrom cinema c1,cinema c2where c1.free=True and c2.free=True and(c1.seat_id-c2.seat_id=1 orc2.seat_id-c1....

1、题目描述来源：力扣（LeetCode）2、解题思路查什么比率，都是一样的套路，先查数量count(distinct requester_id,accepter_id)，记得去重后进行商运算，记得处理空值，然后小数位round(ifnull(count(.....)3、提交记录select round(ifnull(count(distinct requester_id,acce...

1、题目描述来源：力扣（LeetCode）2、解题思路挺简单的，主要是记得去重count(distinct class,student) as cou3、提交记录select classfrom(select class,count(distinct class,student) as coufrom coursesgroup by class)add_couwhere co...

1、题目描述来源：力扣（LeetCode）2、解题思路☺3、提交记录select name,population,areafrom Worldwhere area>3000000 or population>25000000389ms

1、题目描述来源：力扣（LeetCode）2、解题思路就是两个条件，不无聊，奇数：where description<>'boring' and id%2=13、提交记录select *from cinemawhere description<>'boring' and id%2=1order by rating desc228ms...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 其实就是两两编号互换，对每个数，奇数就加1，偶数就减1，然后排序select *,if(id%2=1,id+1,id-1) as jude from seat order by jude2# 上述的方式，如果id总数是奇数，最后的数不成对，就会出现最后一个数没有排列好例如：1/2/3/4/5，就会变成2/1/4/3/6...

1、题目描述We define the install date of a player to be the first login day of that player.We also define day 1 retention of some date X to be the number of players whose install date is X and they logge...

1、题目描述Write an SQL query that reports the products that were only sold in spring 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.The query result format is in the following example:Produ...

1、题目描述Write an SQL query that reports the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products present in the Product table.The query result format is in the following exam...

1、题目描述Write an SQL query that reports the best seller by total sales price, If there is a tie, report them all.The query result format is in the following example:Product table:product_idprod...

1、题目描述Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.The query result format is in...

1、题目描述Write an SQL query that reports all the projects that have the most employees.The query result format is in the following example:Project table:project_idemployee_id111213...

1、题目描述Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.The query result format is in the following example:Project table:...

1、题目描述Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold.The query result format is in the following example:Sales table:sale_id...

1、题目描述Write an SQL query that reports the total quantity sold for every product id.The query result format is in the following example:Sales table:sale_idproduct_idyearquantityprice1...

1、题目描述Write an SQL query that reports all product names of the products in the Sales table along with their selling year and price.For example:Sales table:sale_idproduct_idyearquantitypric...

1、题目描述写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)示例：actor_iddirector_idtimestamp110111112123124215216Result 表：actor_iddirector_id...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 首先对Customer去重，select distinct * from customer2# 然后，统计每个客人购买的货物种类数量select customer_id,count(product_key) cou13# 增加子表，对Product统计所有货物种类select count(*) as cou2 from Pr...

1、题目描述来源：力扣（LeetCode）2、解题思路if语句，或者case when then end也行3、提交记录update salaryset sex=if(sex='m','f','m'）系统不能执行，不知道怎么回事...

1、题目描述来源：力扣（LeetCode）2、解题思路简单的题目，直接写的进阶解答方式1# 首先，对customer_number分组，统计数量2# 对数量进行排名，参考 178. 分数排名3# 取排名为1的记录3、提交记录select customer_numberfrom(select add_cou.*,(@n:=case when @j=(@j:=cou) the...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 第一个子表，按TIV_2015分组，统计数量，取大于1的值2# 第二个子表，按LAT,LON分组，统计数量，取等于1的值3# 2表联查3、提交记录select sum(TIV_2016) as TIV_2016from(select TIV_2015,count(*) as cou_2015from insu...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 计算取消数量：sum(case when `Status` like 'cancelled_by_%' then 1 else 0 end）；与总数的比值作为取消率count(Id)；用round（）函数，取2为小数2# Users u作为乘客表，Users u1作为司机表，与Trips t表联查司机和乘客Banned='...

1、题目描述来源：力扣（LeetCode）2、解题思路2表联查，使用函数datediff(w1.recorddate,w2.recorddate)=1，限定日期相减等于13、提交记录select w1.Idfrom weather w1,weather w2where datediff(w1.recorddate,w2.recorddate)=1 and w1.Temperatur...

1、题目描述来源：力扣（LeetCode）2、解题思路2表联查，删除ID不同，但是Email相同的数据3、提交记录delete p1from Person p1,Person p2where p1.Id>p2.Id and p1.Email=p2.Email...

1、题目描述来源：力扣（LeetCode）2、解题思路思路一1# 创建子查询，Employee，e，e12表联查，count计算所有大于当前e.Salary的数量，即为当前e.Salary的排名（从0开始），令其<3,即为前3select count(distinct e1.Salary) from Employee e1where e1.Salary>e.Salary...

1、题目描述来源：力扣（LeetCode）2、解题思路1# 使用子查询，找出各部门的最高工资select max(Salary) Salary,DepartmentId from Employee group by DepartmentId作为表z2# 3表联查，Department d,Employee e3、提交记录select d.Name Department,e.Name ...

1、题目描述来源：力扣（LeetCode）2、解题思路使用子查询，找出Orders表的ID；然后查询Customers表中不在not in子查询的Name。3、提交记录select Name as Customersfrom Customerswhere Id not in (select CustomerId from Orders)...

1、题目描述来源：力扣（LeetCode）2、解题思路思路一2表联查，条件是Email相等，ID不等思路二优化一下耗时，将查询条件的<>改为<3、提交记录思路一select distinct e1.Emailfrom Person e1,Person e2where e1.Email=e2.Email and e1.Id<>e2.Id;思...

1、题目描述来源：力扣（LeetCode）2、解题思路2表联查，e1作为员工表，e2作为经理表；则连接条件为 e1.ManagerId=e2.Id，比较条件为e1.Salary>e2.Salary3、提交记录select e1.Name as Employeefrom Employee e1,Employee e2where e1.Salary>e2.Salary a...

1、题目描述来源：力扣（LeetCode）2、解题思路3表联查，Num相同，Id连续。（Logs为关键字，需要反单引号`）3、提交记录select distinct l1.Num as ConsecutiveNumsfrom `Logs` l1,`Logs` l2,`Logs` l3where l1.Num=l2.Num and l2.Num=l3.Num and l1.Id=l2...