HansmV-CSDN博客

原创 1084. 销售分析III-sql

select p.product_id, product_name from Product as p where p.product_id not in (select distinct s.product_id from Sales as s where sale_date not between '2019-01-01' and '2019-03-31' );selectp.product_id,product_namefromProductaspwherep.p...

2021-06-10 12:19:14 138

原创 1083. 销售分析 II-sql

-- better oneselect buyer_id from Sales left outer join Product on Sales.product_id = Product.product_id group by buyer_id having sum(product_name='S8')>0 and sum(product_name='iPhone')=0;-- mineselect distinct buyer_id from Saleswhere bu...

2021-06-10 12:17:17 163

原创 1082. 销售分析 I -sql

select seller_id from Salesgroup by seller_idhaving sum(price) = (select sum(price) as Sprice from Sales group by seller_idorder by Sprice desc limit 1);selectseller_idfromSalesgroupbyseller_idhavingsum(price)=(selectsum(price)asSp...

2021-06-10 12:15:11 141

原创 1076. 项目员工II-sql

select project_id from Projectgroup by project_idhaving count(*) = (select count(*) as amount from Project group by project_idorder by amount desclimit 1);selectproject_idfromProjectgroupbyproject_idhavingcount(*)=(selectcount(*)as...

2021-06-10 12:13:32 116

原创 1075. 项目员工 I-sql

select project_id, round(avg(experience_years), 2) as average_yearsfrom Project as p left join Employee as eon p.employee_id = e.employee_idgroup by project_id;selectproject_id,round(avg(experience_years),2)asaverage_yearsfromProjectasp...

2021-06-10 12:11:56 120

原创 1069. 产品销售分析 II-sql

select product_id, sum(quantity) as total_quantityfrom Sales group by product_id;selectproduct_id,sum(quantity)astotal_quantityfromSalesgroupbyproduct_id;

2021-06-10 12:10:17 114

原创 1068. 产品销售分析 I-sql

select product_name, year, price from Sales as s left join Product as pon s.product_id = p.product_id;selectproduct_name,year,pricefromSalesassleftjoinProductaspons.product_id=p.product_id;

2021-06-10 12:08:34 111

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

select actor_id, director_id from ActorDirector group by actor_id, director_idhaving count(*) >= 3;selectactor_id,director_idfromActorDirectorgroupbyactor_id,director_idhavingcount(*)>=3;

2021-06-10 12:06:28 84

原创 627. 变更性别-sql

update salaryset sex = (case when sex = 'm' then 'f' else 'm' end);updatesalarysetsex=(casewhensex='m'then'f'else'm'end);

2021-06-10 12:04:54 132

原创 620. 有趣的电影-sql

select* from cinemawhere description <> 'boring' and mod(id,2) = 1 order by rating desc;select*fromcinemawheredescription<>'boring'andmod(id,2)=1orderbyratingdesc;

2021-06-10 12:03:36 86

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

select ifnull((select num from (select num from my_numbers group by num having count(*) = 1 order by num desc ) as table1 limit 1), null) as num;selectifnull((selectnumfrom(selectnumfrommy_numbers...

2021-06-09 05:43:07 108

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

select distinct abs(p1.x - p2.x) as shortest from point as p1 join point as p2 order by shortest limit 1,1;selectdistinctabs(p1.x-p2.x)asshortestfrompointasp1joinpointasp2orderbyshortestlimit1,1;

2021-06-09 05:40:53 83

原创 610. 判断三角形-sql

select *,case when (y + z > x and x + z > y and x + y > z) then 'Yes' else 'No' end as trianglefrom triangle;select*,casewhen(y+z>xandx+z>yandx+y>z)then'Yes'else'No'endastrianglefromtriangle;...

2021-06-09 05:38:11 235

原创 607. 销售员-sql

select distinct s.name from salesperson as s where s.sales_id not in(select distinct o.sales_id from orders as o where o.com_id in( select c.com_id from company as c where c.name = 'Red' ));selectdistincts.namefromsalespersonasswh...

2021-06-09 05:36:39 97

原创 603. 连续空余座位-sql

select distinct c1.seat_id from cinema as c1, cinema as c2where c1.free = 1 and c2.free = 1 and (c1.seat_id = c2.seat_id +1 or c1.seat_id = c2.seat_id - 1)order by seat_id;selectdistinctc1.seat_idfromcinemaasc1,cinemaasc2wherec1.fr...

2021-06-09 05:34:08 357

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

select round( ifnull( (select count(distinct requester_id, accepter_id) from RequestAccepted) / (select count(distinct sender_id, send_to_id) from FriendRequest) , 0),2) as accept_rate;selectround(ifnull((...

2021-06-09 05:31:32 208

原创 596. 超过5名学生的课-sql

select class from coursesgroup by classhaving count(distinct student) >= 5;selectclassfromcoursesgroupbyclasshavingcount(distinctstudent)>=5;

2021-06-09 05:27:49 210

原创 595. 大的国家-sql

select name, population, area from Worldwhere area > 3000000 or population > 25000000;selectname,population,areafromWorldwherearea>3000000orpopulation>25000000;

2021-06-09 05:25:05 188

原创 586. 订单最多的客户-sql

select customer_number from orders group by customer_numberorder by count(customer_number) desclimit 1;selectcustomer_numberfromordersgroupbycustomer_numberorderbycount(customer_number)desclimit1;

2021-06-09 05:08:01 224

原创 584. 寻找用户推荐人-sql

select name from customer where referee_id <> 2 or referee_id is null;selectnamefromcustomerwherereferee_id<>2orreferee_idisnull;

2021-06-09 05:06:17 210

原创 511. 游戏玩法分析 I-sql

select player_id, min(event_date) as first_loginfrom Activitygroup by player_id;

2021-06-08 05:43:22 361

原创 577. 员工奖金-sql

select name, bonus from Employee as e left join Bonus as b on e.empId = b.empIdwhere bonus is null or bonus < 1000;selectname,bonusfromEmployeeaseleftjoinBonusasbone.empId=b.empIdwherebonusisnullorbonus<1000;...

2021-06-08 05:38:52 396

原创 512. 游戏玩法分析 II-sql

select player_id, device_id from Activitywhere (player_id, event_date) in (select player_id, min(event_date) as event_date from Activity group by Player_id)group by player_id;selectplayer_id,device_idfromActivitywhere(player_id,event_date)i...

2021-06-08 05:37:30 344

原创 197. 上升的温度-sql

select w1.id from Weather as w1, Weather as w2 where DATEDIFF(w1.recordDate, w2.recordDate) = 1 and w2.Temperature < w1.Temperature;selectw1.idfromWeatherasw1,Weatherasw2whereDATEDIFF(w1.recordDate,w2.recordDate)=1andw2.Temperatur...

2021-06-08 05:33:24 260

原创 196. 删除重复的电子邮箱-sql

delete p2 from Person as p1, Person as p2 where p2.Id > p1.Id and p2.Email = p1.Email;deletep2fromPersonasp1,Personasp2wherep2.Id>p1.Idandp2.Email=p1.Email;

2021-06-08 05:31:33 330

原创 183. 从不订购的客户-sql

select Name as Customersfrom Customerswhere Id not in(select CustomerId from Orders);selectNameasCustomersfromCustomerswhereIdnotin(selectCustomerIdfromOrders);

2021-06-08 05:29:03 44

原创 182. 查找重复的电子邮箱-sql

select Emailfrom Persongroup by Email having count(Email) > 1;selectEmailfromPersongroupbyEmailhavingcount(Email)>1;

2021-06-08 05:27:10 84

原创 181.-超过经理收入的员工-sql

select Name as Employeefrom Employee as e1where exists (select * from Employee as e2 where e1.Salary > e2.Salary and e1.ManagerId = e2.Id);selectNameasEmployeefromEmployeease1whereexists...

2021-06-08 05:25:38 60

原创 176-第二高的薪水-sql

select ifnull((select distinct Salary from Employee order by Salary desc limit 1,1), null) as SecondHighestSalary ;selectifnull((selectdistinctSalaryfromEmployeeorderbySalarydesclimit1,1),null)asSecondHighestSalary;

2021-06-08 05:23:20 45

原创 175-组合两个表-sql

select p.FirstName, p.LastName, a.City, a.Statefrom Person as p left join Address as aon p.PersonId = a.PersonId;selectp.FirstName,p.LastName,a.City,a.StatefromPersonaspleftjoinAddressasaonp.PersonId=a.PersonId;

2021-06-08 05:19:11 78

HansmV的博客