MySql结合element-plus pagination的分页查询

实现效果如下：

重点：使用mysql查询的limit和offset

原生SQL写法：

select c.id as deptid,c.name as department,position,a.name staffname,'2024-11' as shijian
,CASE WHEN b.shijian IS NULL THEN 'no' ELSE 'yes' END AS submit
from fa_wecom_staff a left JOIN fa_kpi_table b on a.userid=b.staff_id AND shijian = '2024-11' 
LEFT JOIN fa_wecom_department c ON a.department=c.id
WHERE phone<>'' AND b.shijian IS NOT NULL
GROUP BY a.name,userid,b.shijian
LIMIT 100 OFFSET 1;

limit对应分页插件的参数pageSize，offset对应分页参数的pageNum，计算公式如下：

offset = pageNum==1 ? 1 : (pageNum-1)*pageSize;

后端写法（php）

    public function searchnotsubmit($pageNum=1,$pageSize=10,$shijian=null,$department_id=null,$department_value=null,$status=null){$where = ' AND 1=1';if(!empty($department_id)){$where .= ' AND c.id in ('.$department_id.')';}if(!empty($department_value)){$where .= ' AND c.name in ("'.$department_value.'")';}if(!empty($status)){if($status=='未提交') $where .= ' AND b.shijian IS NULL';if($status=='已提交') $where .= ' AND b.shijian IS NOT NULL';}$sql = "select c.id as deptid,c.name as department,position,a.name staffname,'$shijian' as shijian
,CASE WHEN b.shijian IS NULL THEN '未提交' ELSE '已提交' END AS submit
from fa_wecom_staff a left JOIN fa_kpi_table b on a.userid=b.staff_id AND shijian = '$shijian' 
LEFT JOIN fa_wecom_department c ON a.department=c.id
WHERE phone<>'' $where GROUP BY a.name,userid,b.shijian";$total = count(Db::query($sql));$offset = $pageNum==1 ? 1 : ($pageNum-1)*$pageSize;$row = Db::query($sql." LIMIT $pageSize OFFSET $offset;");$this->success('查询成功',['total'=>$total,'data'=>$row]);}

前端局部代码（pagination分页组件）

<el-pagination @size-change="onHandleSizeChange" @current-change="onHandleCurrentChange" class="mt15":pager-count="10" :page-sizes="[10, 20, 30, 50, 100, 200, 500]" v-model:current-page="search_params.pageNum" backgroundv-model:page-size="search_params.pageSize" layout="total, sizes, prev, pager, next, jumper":total="tableData.total"></el-pagination>

完整前端页面（vue3）

<template><div v-if="1" class="system-dic-container layout-padding"><NoticeBartext="🎉🎉🔥功能更新： "/><el-segmented v-if="true" v-model="value" :options="options" size="default" @change="handleSp(value)" /><el-card shadow="hover" class="layout-padding-auto"><div v-if="1" class="system-user-search d_flex mb15"><div class="search_info mb15"><div class="inline_block mb15"><span>部门下拉：</span><el-cascaderv-model="search_params.d":options="alldepartment":props="alldepartmentProps"show-all-levels="false"placeholder="下拉/输入部门名称"filterable@change="handleChangeDept"/></div><div class="inline_block mb15"><span>部门：</span><el-inputv-model="search_params.department_value"style="width: 240px"placeholder="请输入部门名称"clearable/></div><!-- <div class="ml10 inline_block mb15"><span>部门成员：</span><el-inputv-model="search_params.staff_value"style="width: 240px"placeholder="请输入姓名"clearable/></div>--><div class="ml10 inline_block mb15"><span>流程状态：</span><el-select v-model="search_params.status" :filterable="true" placeholder=""style="width: 240px"><el-optionv-for="(item,index) in ['已提交','未提交']":key="index":label="item":value="item"/></el-select></div> <div class="ml10 inline_block mb15"><span>绩效日期：</span><el-date-picker v-model="search_params.shijian" type="month" placeholder="选择绩效年月"@change="slideMonthFun"></el-date-picker></div></div><div class="search_btn"><el-button size="large" type="primary" class="ml10" @click="searchFun()"><el-icon><ele-Search /></el-icon>查询</el-button><el-button size="large" type="success" class="ml10" @click="resetFun()"><el-icon><ele-RefreshRight /></el-icon>重置</el-button></div></div><el-table id="my-table1" :data="tableData.data" v-loading="tableData.loading" style="width: 100%":height="cardDetailsTableHeight ? cardDetailsTableHeight-100 : 500"><!-- <el-table-column type="selection" width="55" /> --><el-table-column type="index" label="序号" width="80" /><el-table-column prop="department" label="部门" show-overflow-tooltip></el-table-column><el-table-column prop="position" label="岗位名称" show-overflow-tooltip></el-table-column><el-table-column prop="staffname" label="姓名" show-overflow-tooltip></el-table-column><el-table-column prop="submit" label="状态" show-overflow-tooltip><template #default="scope"><el-tag type="success" v-if="scope.row.submit == '已提交'">已提交</el-tag><el-tag type="error" v-else-if="scope.row.submit == '未提交'">未提交</el-tag></template></el-table-column><el-table-column prop="shijian" label="绩效日期" show-overflow-tooltip></el-table-column><el-table-column label="操作" width="150"><template #default="scope"><el-button size="small" text type="primary"@click="preview">查看</el-button><el-button size="small" text type="primary"@click="notice">发通知</el-button></template></el-table-column></el-table><el-pagination @size-change="onHandleSizeChange" @current-change="onHandleCurrentChange" class="mt15":pager-count="10" :page-sizes="[10, 20, 30, 50, 100, 200, 500]" v-model:current-page="search_params.pageNum" backgroundv-model:page-size="search_params.pageSize" layout="total, sizes, prev, pager, next, jumper":total="tableData.total"></el-pagination></el-card><DicDialog ref="dicDialogRef" @refresh="searchFun()" /></div></template><script setup lang="ts" name="systemDic">
import { defineAsyncComponent, reactive, onMounted, ref,nextTick } from 'vue';
import { ElMessage, ElNotification } from 'element-plus';
import { Session } from '/@/utils/storage';
import { formatDate } from '/@/utils/formatTime';
import FileSaver from 'file-saver'
import * as XLSX from 'xlsx';
import { getAlldepartmentlistApi, hrApi } from '/@/api/kpi/index';
// 引入组件
const DicDialog = defineAsyncComponent(() => import('/@/views/hr/dialog/dialogKPITemplate.vue'));
const NoticeBar = defineAsyncComponent(() => import('/@/components/noticeBar/index.vue'));
const value = ref('全部')const options = ['全部', '已提交', '未提交']
function handleSp(e: any) {if (e == '全部') {search_params.value.status = ''getTableData()} else {search_params.value.status = egetTableData()}
}const alldepartment = ref(null)
// 定义变量内容
const dicDialogRef = ref();
const tableData = reactive({data: [],total: 0,loading: false,
});// 表格高度
const cardDetailsTableHeight: any = ref()
// 动态设置表格高度
const getCardDetailsTableHeight = () => {let tableH = 370; //距离页面下方的高度let tableHeightDetil = window.innerHeight - tableH;if (tableHeightDetil <= 100) {cardDetailsTableHeight.value = 100;} else {cardDetailsTableHeight.value = window.innerHeight - tableH;}
};
// 监听窗口变化动态设置表格高度
window.onresize = () => {getCardDetailsTableHeight()
}
// 查询参数
const search_params = ref({shijian: formatDate(new Date(), 'YYYY-mm'),department_value: '',department_id: '',staff_value: '',status: null,d:null,pageNum: 1,  //当前页码pageSize: 10,  //每页显示数量
})function preview () {ElMessage.info('查看功能尚未开发')
}
function notice () {ElMessage.info('通知功能尚未开发')
}//请求表格数据
const getTableData = async () => {tableData.loading = true;// 默认加上当前审批者idlet res = await hrApi().searchNotSubmit(search_params.value)tableData.loading = false;if (res.code === 1) {tableData.data = res.data.data;tableData.total = res.data.total;} else if (res.code == 0) {ElMessage.error(res.msg)}
};
// 1、初次渲染，获取部门信息
getdepartmentlistFun()
async function getdepartmentlistFun() {getTableData()
}// 查询
const searchFun = () => {getTableData()
}// 重置
const resetFun = () => {search_params.value = {shijian: '',department_value: '',department_id: '',staff_value: '',status: null,d:null,pageNum: 1,  //当前页码pageSize: 10,  //每页显示数量}getTableData()
}// 切换月份
const slideMonthFun = (e: any) => {if (e != null) {search_params.value.shijian = formatDate(e, 'YYYY-mm');}
}// function changePageFun() {
// 	getTableData()
// }// 每页显示数量改变
const onHandleSizeChange = (val: number) => {search_params.value.pageSize = val;getTableData()
};
// 分页改变
const onHandleCurrentChange = (val: number) => {search_params.value.pageNum = val;getTableData()
};// const alldepartmentProps = {multiple: true,emitPath:true}
// // 部门下拉改变
const handleChangeDept = (val: any) => {search_params.value.department_id = val.join(',');
};
// 页面加载时
onMounted(() => {getCardDetailsTableHeight()nextTick(() => {// console.log('DOM已更新');})getAlldepartmentlistApi().then((res)=>{if(res.code==1){alldepartment.value = res.data}})
});
</script><style lang="scss" scoped>
.calculate_status {background-color: #fff;margin-bottom: 10px;border: 1px solid #e4e7ed;border-radius: 4px;padding: 10px 15px;.item_sum {display: flex;justify-content: space-between;align-items: center;overflow: hidden;.li {display: flex;flex-direction: column;align-items: center;padding: 0 5px;.label {margin-bottom: 7px;font-size: 15px;color: #7f7f7f;}.num {font-size: 18px;color: #535353;&.has_num {color: #e51515;font-weight: bold;}}}}
}.inline_block {display: inline-block;
}.d_flex {display: flex;flex-wrap: wrap;
}
</style>