当前位置:网站首页>每日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;
边栏推荐
猜你喜欢
随机推荐
防火墙-0-管理地址
OA Project Pending Meeting & History Meeting & All Meetings
radix-4 FFT 原理和C语言代码实现
从mask-rcnn到shp
导航定位中的坐标系
grep、sed、awk
HCIP-BGP的选路实验
iptables 使用脚本来管理规则
MySQL导入导出&视图&索引&执行计划
maxwell 概念
Arcgis小工具_实现重叠分析
一个小时快速熟悉MySQL基本用法
OA项目之我的会议(会议排座&送审)
求过去半年内连续30天以上每天都有1000元以上成交的商铺
OA项目之项目简介&会议发布
损失函数——交叉熵
TOP2两数相加
Map Reduce
HCIP MPLS/BGP综合实验
HCIP OSPF/MGRE Comprehensive Experiment