有如下数据库表,自己新增模拟数据,并完成对应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的诉求件的机构名称和诉求件数量
时间: 2024-04-17 21:28:06 浏览: 111
假设我们的数据库中已经存在了以下表:
机构表:
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查询可以满足你的需求。如果有其他问题,请随时提出。