查询部门人数大于3个的部门名单及人员数量
时间: 2024-03-20 11:42:24 浏览: 21
好的,我理解了你的问题。可以通过以下 SQL 语句来实现:
```
SELECT dept_name, COUNT(*) as num_of_employees
FROM employees
GROUP BY dept_name
HAVING COUNT(*) > 3;
```
这条 SQL 语句会返回一个部门人数大于 3 个的部门名单及其人员数量。注意,这里的 `employees` 表是一个假设的表,你需要将其替换为你实际使用的表名。同时,为了保证查询结果的准确性,你需要将表中的字段名和数据类型与上述 SQL 语句中的保持一致。
相关问题
有如下数据库表,自己新增模拟数据,并完成对应SQL 机构表: sys_org(id,name) 诉求事件表: petiton_appeal(id,content,datetime,org_id) 诉求人表: petition_visitor(id,name,id_number,phone,age) 诉求人员关联表: petition_appeal_visitor(id,appeal_id,visitor_id) 1,按月统计各个机构的诉求事件数,按数量降序 2,按月统计诉求件数量大于平均数的人员名单 3,按月统计各个部门诉求人数大于3的诉求件的数量 4,查询某月诉求件数量大于机构名称为A的的机构名称和诉求件数量 5,查询某月存在诉求人年龄小于18的诉求件的机构名称和诉求件数量
以下是对应的SQL查询语句:
1. 按月统计各个机构的诉求事件数,按数量降序:
```sql
SELECT MONTH(p.datetime) AS month, o.name AS org_name, COUNT(*) AS event_count
FROM petition_appeal p
JOIN sys_org o ON p.org_id = o.id
GROUP BY MONTH(p.datetime), o.name
ORDER BY event_count DESC;
```
2. 按月统计诉求件数量大于平均数的人员名单:
```sql
SELECT MONTH(p.datetime) AS month, v.name AS visitor_name
FROM petition_appeal p
JOIN petition_appeal_visitor av ON p.id = av.appeal_id
JOIN petition_visitor v ON av.visitor_id = v.id
GROUP BY MONTH(p.datetime), v.name
HAVING COUNT(*) > (SELECT AVG(count_visitor) FROM (SELECT COUNT(*) AS count_visitor FROM petition_appeal_visitor GROUP BY appeal_id) AS temp);
```
3. 按月统计各个部门诉求人数大于3的诉求件的数量:
```sql
SELECT MONTH(p.datetime) AS month, o.name AS org_name, COUNT(*) AS event_count
FROM petition_appeal p
JOIN sys_org o ON p.org_id = o.id
WHERE p.org_id IN (SELECT org_id FROM petition_appeal_visitor GROUP BY org_id HAVING COUNT(*) > 3)
GROUP BY MONTH(p.datetime), o.name;
```
4. 查询某月诉求件数量大于机构名称为A的机构名称和诉求件数量:
```sql
SELECT MONTH(p.datetime) AS month, o.name AS org_name, COUNT(*) AS event_count
FROM petition_appeal p
JOIN sys_org o ON p.org_id = o.id
WHERE o.name = 'A'
GROUP BY MONTH(p.datetime), o.name
HAVING COUNT(*) > (SELECT COUNT(*) FROM petition_appeal WHERE org_id = o.id);
```
5. 查询某月存在诉求人年龄小于18的诉求件的机构名称和诉求件数量:
```sql
SELECT MONTH(p.datetime) AS month, o.name AS org_name, COUNT(*) AS event_count
FROM petition_appeal p
JOIN sys_org o ON p.org_id = o.id
JOIN petition_appeal_visitor av ON p.id = av.appeal_id
JOIN petition_visitor v ON av.visitor_id = v.id
WHERE MONTH(p.datetime) = '某月' AND v.age < 18
GROUP BY MONTH(p.datetime), o.name;
```
请注意,以上SQL语句中的表名、列名以及某些变量需要根据实际情况进行替换。
有如下数据库表,自己新增模拟数据,并完成对应SQL 机构表: sys_org(id,name) 诉求事件表: petiton_appeal(id,content,datetime,org_id) 诉求人表: petition_visitor(id,name,id_number,phone,age) 诉求人员关联表: petition_appeal_visitor(id,appeal_id,visitor_id) 1,按月统计各个机构的诉求事件数,按数量降序 2,按月统计当月中诉求事件大于当月人员平均诉求数量的人员 3,按月统计各个部门诉求人数大于3的诉求件的数量 4,查询某个月诉求件数量大于机构名称为A的的机构名称和诉求件数量 5,查询某个月存在诉求人年龄小于18的诉求件的机构名称和诉求件数量
假设我们的数据库中已经存在了以下表:
机构表:
sys_org(id, name)
诉求事件表:
petition_appeal(id, content, datetime, org_id)
诉求人表:
petition_visitor(id, name, id_number, phone, age)
诉求人员关联表:
petition_appeal_visitor(id, appeal_id, visitor_id)
为了完成对应的SQL查询,我们首先需要新增一些模拟数据。以下是一些示例数据,你可以按照需要进行调整:
机构表数据:
INSERT INTO sys_org(id, name) VALUES
(1, '机构A'),
(2, '机构B'),
(3, '机构C');
诉求事件表数据:
INSERT INTO petition_appeal(id, content, datetime, org_id) VALUES
(1, '诉求事件1', '2022-01-01', 1),
(2, '诉求事件2', '2022-01-02', 1),
(3, '诉求事件3', '2022-01-03', 2),
(4, '诉求事件4', '2022-02-01', 2),
(5, '诉求事件5', '2022-02-02', 3),
(6, '诉求事件6', '2022-02-03', 3);
诉求人表数据:
INSERT INTO petition_visitor(id, name, id_number, phone, age) VALUES
(1, '诉求人1', 'ID001', '1234567890', 25),
(2, '诉求人2', 'ID002', '9876543210', 30),
(3, '诉求人3', 'ID003', '1111111111', 16),
(4, '诉求人4', 'ID004', '2222222222', 20),
(5, '诉求人5', 'ID005', '3333333333', 45),
(6, '诉求人6', 'ID006', '4444444444', 18);
诉求人员关联表数据:
INSERT INTO petition_appeal_visitor(id, appeal_id, visitor_id) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4),
(5, 3, 5),
(6, 3, 6);
现在,我将为你提供每个问题对应的SQL查询:
1. 按月统计各个机构的诉求事件数,按数量降序:
SELECT EXTRACT(MONTH FROM datetime) AS month,
sys_org.name AS org_name,
COUNT(petition_appeal.id) AS event_count
FROM petition_appeal
JOIN sys_org ON sys_org.id = petition_appeal.org_id
GROUP BY month, org_name
ORDER BY event_count DESC;
2. 按月统计当月中诉求事件大于当月人员平均诉求数量的人员:
SELECT EXTRACT(MONTH FROM datetime) AS month,
petition_visitor.name AS visitor_name
FROM petition_appeal
JOIN petition_appeal_visitor ON petition_appeal_visitor.appeal_id = petition_appeal.id
JOIN petition_visitor ON petition_visitor.id = petition_appeal_visitor.visitor_id
GROUP BY month, visitor_name
HAVING COUNT(petition_appeal.id) > (
SELECT AVG(event_count)
FROM (
SELECT EXTRACT(MONTH FROM datetime) AS month,
petition_visitor.id AS visitor_id,
COUNT(*) AS event_count
FROM petition_appeal
JOIN petition_appeal_visitor ON petition_appeal_visitor.appeal_id = petition_appeal.id
JOIN petition_visitor ON petition_visitor.id = petition_appeal_visitor.visitor_id
GROUP BY month, visitor_id
) AS event_counts
WHERE event_counts.month = month
);
3. 按月统计各个部门诉求人数大于3的诉求件的数量:
SELECT EXTRACT(MONTH FROM datetime) AS month,
sys_org.name AS org_name,
COUNT(petition_appeal.id) AS appeal_count
FROM petition_appeal
JOIN sys_org ON sys_org.id = petition_appeal.org_id
WHERE petition_appeal.id IN (
SELECT appeal_id
FROM petition_appeal_visitor
GROUP BY appeal_id
HAVING COUNT(DISTINCT visitor_id) > 3
)
GROUP BY month, org_name;
4. 查询某个月诉求件数量大于机构名称为A的的机构名称和诉求件数量:
SELECT EXTRACT(MONTH FROM datetime) AS month,
sys_org.name AS org_name,
COUNT(petition_appeal.id) AS appeal_count
FROM petition_appeal
JOIN sys_org ON sys_org.id = petition_appeal.org_id
WHERE sys_org.name = '机构A'
GROUP BY month, org_name
HAVING COUNT(petition_appeal.id) > (
SELECT COUNT(*)
FROM petition_appeal
WHERE EXTRACT(MONTH FROM datetime) = month
);
5. 查询某个月存在诉求人年龄小于18的诉求件的机构名称和诉求件数量:
SELECT EXTRACT(MONTH FROM datetime) AS month,
sys_org.name AS org_name,
COUNT(petition_appeal.id) AS appeal_count
FROM petition_appeal
JOIN sys_org ON sys_org.id = petition_appeal.org_id
WHERE petition_appeal.id IN (
SELECT appeal_id
FROM petition_appeal_visitor
JOIN petition_visitor ON petition_visitor.id = petition_appeal_visitor.visitor_id
WHERE petition_visitor.age < 18
)
GROUP BY month, org_name;
希望以上SQL查询可以满足你的需求。如果有其他问题,请随时提出。
相关推荐
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![application/msword](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![7z](https://img-home.csdnimg.cn/images/20210720083312.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![7z](https://img-home.csdnimg.cn/images/20210720083312.png)