当前位置:网站首页>每日sql -查询至少有5名下属的经理和选举

每日sql -查询至少有5名下属的经理和选举

2022-08-11 05:36:00 吃再多糖也不长胖

每日sql -查询至少有5名下属的经理

背景需求

查询至少有5名下属的经理

DDL

在这里插入图片描述

Create table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);insert into Employee (Id, Name, Department, ManagerId) values (101, 'John', 'A', null);insert into Employee (Id, Name, Department, ManagerId) values (102, 'Dan', 'A', 101);insert into Employee (Id, Name, Department, ManagerId) values (103, 'James', 'A', 101);insert into Employee (Id, Name, Department, ManagerId) values (104, 'Amy', 'A', 101);insert into Employee (Id, Name, Department, ManagerId) values (105, 'Anne', 'A', 101);insert into Employee (Id, Name, Department, ManagerId) values (106, 'Ron', 'B', 101);

SQL 解决代码

select Name from Employee t1 join (select ManagerId from Employee group by ManagerId having count(1) >=5) t2 on t1.Id = t2.ManagerId ;

在这里插入图片描述

#选举

背景

有一张表是选举表,一张表是投票表,选出票数最高的人,

DDL

在这里插入图片描述

Create table If Not Exists Candidate (id int, Name varchar(255));Create table If Not Exists Vote (id int, CandidateId int);insert into Candidate (id, Name) values (1, 'A');insert into Candidate (id, Name) values (2, 'B');insert into Candidate (id, Name) values (3, 'C');insert into Candidate (id, Name) values (4, 'D');insert into Candidate (id, Name) values (5, 'E');insert into Vote (id, CandidateId) values (1, 2);insert into Vote (id, CandidateId) values (2, 44);insert into Vote (id, CandidateId) values (3, 3);insert into Vote (id, CandidateId) values (4, 2);insert into Vote (id, CandidateId) values (5, 5);

解决方案

在这里插入图片描述

SELECT name AS 'Name'
FROM Candidate
JOIN (SELECT Candidateid FROM Vote GROUP BY Candidateid ORDER BY COUNT(*) DESC LIMIT 1 ) AS winner
WHERE Candidate.id = winner.Candidateid;
原网站

版权声明
本文为[吃再多糖也不长胖]所创,转载请带上原文链接,感谢
https://blog.csdn.net/weixin_43859562/article/details/122668283