会议OA项目之会议通知（查询&是否参会&反馈详情）

最新推荐文章于 2024-07-20 20:04:43 发布

呲花1

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量202

点赞数

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/m0_62327548/article/details/126046269

版权

一、会议通知SQL语句

难点：
1、会议通知的查询SQL
如：登录‘张三’账号，就要查出凡是张三是参与者、列席者、主持人中的其中一员，那么都需要查询出来

会议反馈表：t_oa_meeting_feedback

会议信息表：t_oa_meeting_info

1、查询出带id=2的会议信息

select * from t_oa_meeting_info where FIND_IN_SET(2
,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state = 4

2、不管会议是否反馈，都要查询出来，所以用外连接，会议信息表为主

select
IFNULL(f.result,-1),t1.*
from
(select * from t_oa_meeting_info where FIND_IN_SET(2
,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4) t1
left JOIN t_oa_meeting_feedback f on t1.id = f.meetingId
and f.personId = 2
ORDER BY result;

二、会议详情反馈SQL

难点：
2、某会议的反馈详情SQL

分析用到的表：
用户表：t_oa_user
反馈表：t_oa_meeting_feedback
会议信息表：t_oa_meeting_info
查询条件：会议的id
用户表：t_oa_user

1、如查询id为12的会议，所有参与人员的姓名
1.1先拿到所有的参与人员id

select CONCAT(canyuze,',',liexize,',',zhuchiren) from 
t_oa_meeting_info where id = 12

1.2在拿到对应参与人员的姓名

SELECT * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))

3、根据反馈情况进行分组

select
t.result,GROUP_CONCAT(t.name) NAMES
from
(select 
t1.name,IFNULL(f.result,-1) result
FROM
(SELECT * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))) t1
left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 12) t
GROUP BY t.result

三、会议通知后台代码

我们的SQL语句编码已经完成，现在开始后台
导入会议通知的界面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="/common/head.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="${pageContext.request.contextPath }/static/js/meeting/meetingNotify.js"></script>
</head>
<style>
body{
	margin:15px;
}
 .layui-table-cell {height: inherit;}
 .layui-layer-page .layui-layer-content {  overflow: visible !important;}
</style>
<body>
<!-- 搜索栏 -->
<div class="layui-form-item" style="margin:15px 0px;">
  <div class="layui-inline">
    <label class="layui-form-label">会议标题</label>
    <div class="layui-input-inline">
      <input type="hidden" id="personId" value="${user.id }"/>
      <input type="text" id="title" autocomplete="off" class="layui-input">
    </div>
  </div>
  <di