当前位置:网站首页>daily sql - query for managers and elections with at least 5 subordinates
daily sql - query for managers and elections with at least 5 subordinates
2022-08-11 07:14:00 【Eat again polysaccharide also not fat】
每日sql -查询至少有5subordinate manager
背景需求
查询至少有5subordinate manager
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 ;
#选举
背景
There is a table for elections,One form is the voting form,The person with the most votes is elected,
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;
边栏推荐
猜你喜欢
HCIP OSPF/MGRE综合实验
什么是Inductive learning和Transductive learning
OA Project Pending Meeting & History Meeting & All Meetings
HCIP-生成树(802.1D ,标准生成树/802.1W : RSTP 快速生成树/802.1S : MST 多生成树)
拼多多api接口应用示例
抖音关键词搜索商品-API工具
获取拼多多商品信息操作详情
核方法 Kernel method
HCIP MGRE\OSPF Comprehensive Experiment
Concurrent programming in eight-part essay
随机推荐
Amazon Get AMAZON Product Details API Return Value Description
MySQL01
华为防火墙-2-状态检测与会话
姿态解算-陀螺仪+欧拉法
1688商品详情接口
抖音API接口大全
HCIA知识复习
抖音分享口令url API工具
radix-4 FFT 原理和C语言代码实现
求过去半年内连续30天以上每天都有1000元以上成交的商铺
《猪猪1984》NFT 作品集将上线 The Sandbox 市场平台
SATA、SAS、SSD三种硬盘存储性能数据
detectron2,手把手教你训练mask_rcnn
OA项目之会议通知(查询&是否参会&反馈详情)
Find the shops that have sold more than 1,000 yuan per day for more than 30 consecutive days in the past six months
获取拼多多商品信息操作详情
Attitude solution - gyroscope + Euler method
华为防火墙-5-NAT
拼多多API接口(附上我的可用API)
京东商品详情API调用实例讲解