有如下数据库表,自己新增模拟数据,并完成对应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-16 20:29:03 浏览: 20
以下是对应的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语句中的表名、列名以及某些变量需要根据实际情况进行替换。