场景
之前在一个项目中遇到过一个统计功能，需要根据各种条件进行统计。然后采用了Java实现，即是将需要的数据直接从数据库中查询出来，然后用Java进行各种加工处理。实现是实现了但是代码却很复杂、可读性极差。于是，想办法直接在数据库中进行加工处理（使用存储过程 ），然后将处理后的数据预先存入表中（新建）。业务上需要统计的时候，就直接调用存储过程，再从存有处理后的数据的表中查询数据。
Java实现及SQL实现对比
客户端（Java）实现
/**
* @description 检查单统计service实现
* @author Michael Corleone
* @date 2019年12月24日
* @version 1.0.0
*/
@Service
@Transactional
public class ReadOrderStatisticsServiceImpl implements ReadOrderStatisticsService {
@Autowired
ReadOrderStatisticsMapper cssMapper;
@Override
public PageInfo<ChecklistStatisticsDto> getAllByYear(ChecklistStatisticsDto checklistStatisticsDto) {
/*
* 获取当前登录用户所属的用户类型，及该类型对应的查询条件
* 0超管 ：无需查询条件 --->查询出所有的数据
* 1平台运营人员：当前用户id --->查询出属于该运营人员(病例表中所属运营人员ID)的所有数据， 以及不属于任何人的数据
* 2平台其他用户：无需查询条件 --->查询出所有的数据
* 3所属医联体用户：当前用户所属医联体id --->查询出该用户所属医联体对应的数据
*/
SysUser login = (SysUser) SecurityUtils.getSubject().getPrincipal();
if (login == null)
return null; // TODO
Integer sysType = login.getSysType();
Map<String,Integer> condition = new HashMap<String,Integer>();
switch (sysType) {
case 0:
condition.put("sysType", sysType);
break;
case 1:
condition.put("sysType", sysType);
condition.put("mId", login.getId());
break;
case 2:
condition.put("sysType", sysType);
break;
case 3:
condition.put("sysType", sysType);
condition.put("mcbId", login.getMcbId());
break;
}
initDataWithYear(condition);
initDataWithYearMonth(condition);
// 根据条件（包括分页信息）查询检查单统计数据
PageHelper.startPage(checklistStatisticsDto.getPage(), PageUtil.PAGE_SIZE);
List<ChecklistStatisticsDto> nums = cssMapper.queryNumsWithYear(checklistStatisticsDto,condition);
/**
* 初始化当年对应的每月的数据量， 如果当前没有数据 则全部默认值为0
*/
for (ChecklistStatisticsDto csd : nums) {
ChecklistStatisticsDto[] arrays = new ChecklistStatisticsDto[12];
for (int i = 0; i < 12; i++) {
arrays[i] = new ChecklistStatisticsDto();
}
Calendar cal = Calendar.getInstance();
List<ChecklistStatisticsDto> cswymddl = csd.getEachMonthCount();
for (ChecklistStatisticsDto cswymdd : cswymddl) {
int month = Integer.parseInt(cswymdd.getMonths());
arrays[month - 1] = cswymdd;
}
List<ChecklistStatisticsDto> eachMonthCount = Arrays.asList(arrays);
csd.setEachMonthCount(eachMonthCount);
}
PageInfo<ChecklistStatisticsDto> records = new PageInfo<ChecklistStatisticsDto>(nums);
return records;
}
@Override
public PageInfo<ChecklistStatisticsDto> getAll(ChecklistStatisticsDto checklistStatisticsDto) {
/*
* 获取当前登录用户所属的用户类型，及该类型对应的查询条件
* 0超管 ：无需查询条件 --->查询出所有的数据
* 1平台运营人员：当前用户id --->查询出属于该运营人员(病例表中所属运营人员ID)的所有数据， 以及不属于任何人的数据
* 2平台其他用户：无需查询条件 --->查询出所有的数据
* 3所属医联体用户：当前用户所属医联体id --->查询出该用户所属医联体对应的数据
*/
SysUser login = (SysUser) SecurityUtils.getSubject().getPrincipal();
if (login == null)
return null; // TODO
Integer sysType = login.getSysType();
Map<String,Integer> condition = new HashMap<String,Integer>();
switch (sysType) {
case 0:
condition.put("sysType", sysType);
break;
case 1:
condition.put("sysType", sysType);
condition.put("mId", login.getId());
break;
case 2:
condition.put("sysType", sysType);
break;
case 3:
condition.put("sysType", sysType);
condition.put("mcbId", login.getMcbId());
break;
}
initDataWithYearMonth(condition);
intiDataWithYearMonthDay(condition);
// 根据条件（包括分页信息）查询检查单统计数据
PageHelper.startPage(checklistStatisticsDto.getPage(), PageUtil.PAGE_SIZE);
List<ChecklistStatisticsDto> nums = cssMapper.queryNums(checklistStatisticsDto,condition);
/**
* 初始化当月对应的每天的统计数量， 如果当前没有数据 则全部默认值为0
*/
for (ChecklistStatisticsDto csd : nums) {
ChecklistStatisticsDto[] arrays = new ChecklistStatisticsDto[31];
for (int i = 0; i < 31; i++) {
arrays[i] = new ChecklistStatisticsDto();
}
List<ChecklistStatisticsDto> cswymddl = csd.getEachDayCount();
for (ChecklistStatisticsDto cswymdd : cswymddl) {
String createTime = cswymdd.getCreateTime();
int day = Integer.parseInt(createTime.split("-")[2].split(" ")[0]);
arrays[day - 1] = cswymdd;
}
List<ChecklistStatisticsDto> eachDayCount = Arrays.asList(arrays);
csd.setEachDayCount(eachDayCount);
}
PageInfo<ChecklistStatisticsDto> records = new PageInfo<ChecklistStatisticsDto>(nums);
return records;
}
/**
*
* @Description 初始化数据：1、查询小肠胶囊视图（带年）、胃胶囊视图（带年）数据 2、 组装数据 3、删除旧数据 4、插入新组装的数据到表中
* @author John Abruzzi
* @date 2020年1月7日
*/
private void initDataWithYear(Map<String,Integer> condition) {
// 检查单统计 返回集合
List<ChecklistStatisticsDto> checklistNums = new ArrayList<ChecklistStatisticsDto>();
// 查询小肠胶囊集合
List<ChecklistStatisticsDto> ic = cssMapper.getIntestineCountWithYear(condition);
// 查询胃胶囊集合
List<ChecklistStatisticsDto> sc = cssMapper.getStomachCountWithYear(condition);
// 既有小肠胶囊数据又有胃胶囊数据
boolean bothFlag = true;
// 没有小肠胶囊数据
if (ic == null || ic.size() == 0) {
for (ChecklistStatisticsDto css : sc) {
css.setTotalNum(css.getStomachNum());
}
bothFlag = false;
checklistNums = sc;
}
// 没有胃胶囊数据
if (sc == null || sc.size() == 0) {
for (ChecklistStatisticsDto css : ic) {
css.setTotalNum(css.getIntestineNum());
}
bothFlag = false;
checklistNums = ic;
}
if (bothFlag) {
// 临时存放（医联体名称+机构名称+年份+月份）相同的 小肠胶囊和胃胶囊数据，key--->小肠胶囊， value--->胃胶囊
Map<ChecklistStatisticsDto, ChecklistStatisticsDto> temp = new HashMap<ChecklistStatisticsDto, ChecklistStatisticsDto>();
// 相同的数据放入temp临时map中
for (ChecklistStatisticsDto intestine : ic) {
for (ChecklistStatisticsDto stomach : sc) {
if (sameNameAndDateWithYear(intestine, stomach)) {
temp.put(intestine, stomach);
}
}
}
// 组合相同的数据 并放入最终的集合中
Iterator<Entry<ChecklistStatisticsDto, ChecklistStatisticsDto>> it = temp.entrySet().iterator();
while (it.hasNext()) {
Entry<ChecklistStatisticsDto, ChecklistStatisticsDto> entry = it.next();
ChecklistStatisticsDto intestine = entry.getKey();
ChecklistStatisticsDto stomach = entry.getValue();
intestine.setStomachNum(stomach.getStomachNum());
intestine.setTotalNum(intestine.getIntestineNum() + stomach.getStomachNum());
checklistNums.add(intestine);
}
// 忽略相同的部分将 不同的部分设值 并放入最终的集合中，
Set<ChecklistStatisticsDto> intestines = temp.keySet();
ok: for (ChecklistStatisticsDto intestine : ic) { // 小肠胶囊
for (ChecklistStatisticsDto csd : intestines) {
if (sameNameAndDateWithYear(intestine, csd))
continue ok;
}
intestine.setTotalNum(intestine.getIntestineNum());
checklistNums.add(intestine);
}
ok: for (ChecklistStatisticsDto stomach : sc) { // 胃胶囊
for (ChecklistStatisticsDto csd : intestines) {
if (sameNameAndDateWithYear(stomach, csd))
continue ok;
}
stomach.setTotalNum(stomach.getStomachNum());
checklistNums.add(stomach);
}
}
cssMapper.delAllWithYear();// 删除所有旧数据
if (checklistNums.size() > 0) // 如果没有数据则不插入
cssMapper.batchInsertWithYear(checklistNums); // 插入实时数据
}
/**
* @Description 初始化数据：1、查询小肠胶囊视图（带年月）、胃胶囊视图（带年月）数据 2、 组装数据 3、删除旧数据 4、插入新组装的数据到表中
* @author John Abruzzi
* @date 2020年1月6日
*/
private void initDataWithYearMonth(Map<String,Integer> condition) {
// 检查单统计 返回集合
List<ChecklistStatisticsDto> checklistNums = new ArrayList<ChecklistStatisticsDto>();
// 查询小肠胶囊集合
List<ChecklistStatisticsDto> ic = cssMapper.getIntestineCount(condition);
// 查询胃胶囊集合
List<ChecklistStatisticsDto> sc = cssMapper.getStomachCount(condition);
// 既有小肠胶囊数据又有胃胶囊数据
boolean bothFlag = true;
// 没有小肠胶囊数据
if (ic == null || ic.size() == 0) {
for (ChecklistStatisticsDto css : sc) {
css.setTotalNum(css.getStomachNum());
}
bothFlag = false;
checklistNums = sc;
}
// 没有胃胶囊数据
if (sc == null || sc.size() == 0) {
for (ChecklistStatisticsDto css : ic) {
css.setTotalNum(css.getIntestineNum());
}
bothFlag = false;
checklistNums = ic;
}
if (bothFlag) {
// 临时存放（医联体名称+机构名称+年份+月份）相同的 小肠胶囊和胃胶囊数据，key--->小肠胶囊， value--->胃胶囊
Map<ChecklistStatisticsDto, ChecklistStatisticsDto> temp = new HashMap<ChecklistStatisticsDto, ChecklistStatisticsDto>();
// 相同的数据放入temp临时map中
for (ChecklistStatisticsDto intestine : ic) {
for (ChecklistStatisticsDto stomach : sc) {
if (sameNameAndDate(intestine, stomach)) {
temp.put(intestine, stomach);
}
}
}
// 组合相同的数据 并放入最终的集合中
Iterator<Entry<ChecklistStatisticsDto, ChecklistStatisticsDto>> it = temp.entrySet().iterator();
while (it.hasNext()) {
Entry<ChecklistStatisticsDto, ChecklistStatisticsDto> entry = it.next();
ChecklistStatisticsDto intestine = entry.getKey();
ChecklistStatisticsDto stomach = entry.getValue();
intestine.setStomachNum(stomach.getStomachNum());
intestine.setTotalNum(intestine.getIntestineNum() + stomach.getStomachNum());
checklistNums.add(intestine);
}
// 忽略相同的部分将 不同的部分设值 并放入最终的集合中，
Set<ChecklistStatisticsDto> intestines = temp.keySet();
ok: for (ChecklistStatisticsDto intestine : ic) { // 小肠胶囊
for (ChecklistStatisticsDto csd : intestines) {
if (sameNameAndDate(intestine, csd))
continue ok;
}
intestine.setTotalNum(intestine.getIntestineNum());
checklistNums.add(intestine);
}
ok: for (ChecklistStatisticsDto stomach : sc) { // 胃胶囊
for (ChecklistStatisticsDto csd : intestines) {
if (sameNameAndDate(stomach, csd))
continue ok;
}
stomach.setTotalNum(stomach.getStomachNum());
checklistNums.add(stomach);
}
}
cssMapper.delAll();// 删除所有旧数据
if (checklistNums.size() > 0) // 如果没有数据则不插入
cssMapper.batchInsert(checklistNums); // 插入实时数据
}
/**
*
* @Description 初始化数据：1、查询小肠胶囊视图（带年月日）、胃胶囊视图（带年月日）数据 2、 组装数据 3、删除旧数据
* 4、插入新组装的数据到表中
* @author John Abruzzi
* @date 2020年1月7日
*/
private void intiDataWithYearMonthDay(Map<String,Integer> condition) {
// 检查单统计 返回集合
List<ChecklistStatisticsDto> checklistNums = new ArrayList<ChecklistStatisticsDto>();
// 查询小肠胶囊集合
List<ChecklistStatisticsDto> ic = cssMapper.getIntestineCountWithYearMonthDay(condition);
// 查询胃胶囊集合
List<ChecklistStatisticsDto> sc = cssMapper.getStomachCountWithYearMonthDay(condition);
// 既有小肠胶囊数据又有胃胶囊数据
boolean bothFlag = true;
// 没有小肠胶囊数据
if (ic == null || ic.size() == 0) {
for (ChecklistStatisticsDto css : sc) {
css.setTotalNum(css.getStomachNum());
}
bothFlag = false;
checklistNums = sc;
}
// 没有胃胶囊数据
if (sc == null || sc.size() == 0) {
for (ChecklistStatisticsDto css : ic) {
css.setTotalNum(css.getIntestineNum());
}
bothFlag = false;
checklistNums = ic;
}
if (bothFlag) {
// 临时存放（医联体名称+机构名称+年份+月份）相同的 小肠胶囊和胃胶囊数据，key--->小肠胶囊， value--->胃胶囊
Map<ChecklistStatisticsDto, ChecklistStatisticsDto> temp = new HashMap<ChecklistStatisticsDto, ChecklistStatisticsDto>();
// 相同的数据放入temp临时map中
for (ChecklistStatisticsDto intestine : ic) {
for (ChecklistStatisticsDto stomach : sc) {
if (sameNameAndDateWithYearMonthDay(intestine, stomach)) {
temp.put(intestine, stomach);
}
}
}
// 组合相同的数据 并放入最终的集合中
Iterator<Entry<ChecklistStatisticsDto, ChecklistStatisticsDto>> it = temp
.entrySet().iterator();
while (it.hasNext()) {
Entry<ChecklistStatisticsDto, ChecklistStatisticsDto> entry = it.next();
ChecklistStatisticsDto intestine = entry.getKey();
ChecklistStatisticsDto stomach = entry.getValue();
intestine.setStomachNum(stomach.getStomachNum());
intestine.setTotalNum(intestine.getIntestineNum() + stomach.getStomachNum());
checklistNums.add(intestine);
}
// 忽略相同的部分将 不同的部分设值 并放入最终的集合中，
Set<ChecklistStatisticsDto> intestines = temp.keySet();
ok: for (ChecklistStatisticsDto intestine : ic) { // 小肠胶囊
for (ChecklistStatisticsDto csd : intestines) {
if (sameNameAndDateWithYearMonthDay(intestine, csd))
continue ok;
}
intestine.setTotalNum(intestine.getIntestineNum());
checklistNums.add(intestine);
}
ok: for (ChecklistStatisticsDto stomach : sc) { // 胃胶囊
for (ChecklistStatisticsDto csd : intestines) {
if (sameNameAndDateWithYearMonthDay(stomach, csd))
continue ok;
}
stomach.setTotalNum(stomach.getStomachNum());
checklistNums.add(stomach);
}
}
cssMapper.delAllWithYearMonthDay();// 删除所有旧数据
if (checklistNums.size() > 0) // 如果没有数据则不插入
cssMapper.batchInsertWithYearMonthDay(checklistNums); // 插入实时数据
}
/**
*
* @Description 判断（医联体名称+机构名称+年份+月份） 是否相等
* @param one
* @param another
* @return
* @author John Abruzzi
* @date 2019年12月24日
*/
private boolean sameNameAndDate(ChecklistStatisticsDto one, ChecklistStatisticsDto another) {
/*
* 判断时间是否为空
*/
if(one.getYears() == null || one.getMonths() == null || another.getYears() == null || another.getMonths() == null) {
return false;
}
try {
if (one.getMcbName().trim().equals(another.getMcbName().trim())
// && one.getOrgName().trim().equals(another.getOrgName().trim())
&& one.getExpectName().trim().equals(another.getExpectName().trim())
&& one.getYears().trim().equals(another.getYears())
&& one.getMonths().trim().equals(another.getMonths().trim())) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
/**
*
* @Description 判断（医联体名称+机构名称+创建时间--年月日） 是否相等
* @param one
* @param another
* @return
* @author John Abruzzi
* @date 2019年12月24日
*/
private boolean sameNameAndDateWithYearMonthDay(ChecklistStatisticsDto one,
ChecklistStatisticsDto another) {
/*
* 判断时间是否为空
*/
if(one.getCreateTime() == null || another.getCreateTime() == null) {
return false;
}
try {
if (one.getMcbName().trim().equals(another.getMcbName().trim())
// && one.getOrgName().trim().equals(another.getOrgName().trim())
&& one.getExpectName().trim().equals(another.getExpectName().trim())
&& one.getCreateTime().equals(another.getCreateTime())) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
/**
*
* @Description 判断（医联体名称+机构名称+年份） 是否相等
* @param one
* @param another
* @return
* @author John Abruzzi
* @date 2019年12月24日
*/
private boolean sameNameAndDateWithYear(ChecklistStatisticsDto one,
ChecklistStatisticsDto another) {
/*
* 判断时间是否为空
*/
if(one.getYears() == null || another.getYears() == null ) {
return false;
}
try {
if (one.getMcbName().trim().equals(another.getMcbName().trim())
// && one.getOrgName().trim().equals(another.getOrgName().trim())
&& one.getExpectName().trim().equals(another.getExpectName().trim())
&& one.getYears().trim().equals(another.getYears().trim())) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
@Override
public String[] getAllYears() {
/*
* 获取当前登录用户所属的用户类型，及该类型对应的查询条件
* 0超管 ：无需查询条件 --->查询出所有的数据
* 1平台运营人员：当前用户id --->查询出属于该运营人员(病例表中所属运营人员ID)的所有数据， 以及不属于任何人的数据
* 2平台其他用户：无需查询条件 --->查询出所有的数据
* 3所属医联体用户：当前用户所属医联体id --->查询出该用户所属医联体对应的数据
*/
SysUser login = (SysUser) SecurityUtils.getSubject().getPrincipal();
if (login == null)
return null; // TODO
Integer sysType = login.getSysType();
Map<String,Integer> condition = new HashMap<String,Integer>();
switch (sysType) {
case 0:
condition.put("sysType", sysType);
break;
case 1:
condition.put("sysType", sysType);
condition.put("mId", login.getId());
break;
case 2:
condition.put("sysType", sysType);
break;
case 3:
condition.put("sysType", sysType);
condition.put("mcbId", login.getMcbId());
break;
}
String[] allYears = cssMapper.getAllYears(condition);
return allYears;
}
}
数据库（SQL）实现
主要使用了3个存储过程实现
- 第一个
CREATE DEFINER = `root` @`%` PROCEDURE `statics_proce_year_readorder` ( ) BEGIN
DECLARE
done BOOLEAN DEFAULT 0;
DECLARE
var_mcb_id INT;
DECLARE
var_m_id INT;
DECLARE
var_mcb_name VARCHAR ( 50 );
DECLARE
var_org_name VARCHAR ( 50 );
DECLARE
var_expect_id INT;
DECLARE
var_expect_name VARCHAR ( 30 );
DECLARE
var_intestine_num INT;
DECLARE
var_stomach_num INT;
DECLARE
var_total_num INT;
DECLARE
var_years VARCHAR ( 4 );
DECLARE
intestine_view_cursor CURSOR FOR SELECT
mcb.id AS mcb_id,
mcb.NAME AS mcb_name,
org.`name` AS org_name,
ept.username AS expect_name,
ept.id AS expect_id,
c.m_id AS m_id,
DATE_FORMAT( c.create_time, '%Y' ) AS years,
COUNT( c.inspection_subjects ) AS intestine_num
FROM
`case` c,
organization org,
medical_conjoined_body mcb,
expect ept,
checklist cl,
endoscopicreport er
WHERE
c.inspection_subjects = '小肠胶囊'
AND c.imcd_id = mcb.id
AND org.id = c.inspection_organization_id
AND c.expert_id = ept.id
AND cl.case_id = c.id
AND er.check_no = cl.check_no
AND er.audit_status = 2
GROUP BY
DATE_FORMAT( c.create_time, '%Y' ),
ept.username,
mcb.`name`;
DECLARE
stomach_view_cursor CURSOR FOR SELECT
mcb.id AS mcb_id,
mcb.NAME AS mcb_name,
org.`name` AS org_name,
ept.username AS expect_name,
ept.id AS expect_id,
c.m_id AS m_id,
DATE_FORMAT( c.create_time, '%Y' ) AS years,
COUNT( c.inspection_subjects ) AS stomach_num
FROM
`case` c,
organization org,
medical_conjoined_body mcb,
expect ept,
checklist cl,
endoscopicreport er
WHERE
c.inspection_subjects = '胃胶囊'
AND c.imcd_id = mcb.id
AND org.id = c.inspection_organization_id
AND c.expert_id = ept.id
AND cl.case_id = c.id
AND er.check_no = cl.check_no
AND er.audit_status = 2
GROUP BY
DATE_FORMAT( c.create_time, '%Y' ),
ept.username,
mcb.`name`;
DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1;
OPEN intestine_view_cursor;
REPEAT
FETCH intestine_view_cursor INTO var_mcb_id,
var_mcb_name,
var_org_name,
var_expect_name,
var_expect_id,
var_m_id,
var_years,
var_intestine_num;
INSERT IGNORE INTO checklistCountWithYearOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, years, intestine_num, stomach_num, total_num )
VALUES
( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_intestine_num, 0, var_intestine_num );
UNTIL done
END REPEAT;
CLOSE intestine_view_cursor;
SET done = 0;
OPEN stomach_view_cursor;
REPEAT
FETCH stomach_view_cursor INTO var_mcb_id,
var_mcb_name,
var_org_name,
var_expect_name,
var_expect_id,
var_m_id,
var_years,
var_stomach_num;
INSERT INTO checklistCountWithYearOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, years, intestine_num, stomach_num, total_num )
VALUES
( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, 0, var_stomach_num, var_stomach_num )
ON DUPLICATE KEY UPDATE stomach_num = var_stomach_num,
total_num = intestine_num + var_stomach_num;
UNTIL done
END REPEAT;
CLOSE stomach_view_cursor;
SET done = 0;
END
- 第二个
CREATE DEFINER = `root` @`%` PROCEDURE `statics_proce_year_month_readorder` ( IN sysType INT, IN mId INT, IN mcbId INT ) BEGIN
DECLARE
done BOOLEAN DEFAULT 0;
DECLARE
var_mcb_id INT;
DECLARE
var_m_id INT;
DECLARE
var_mcb_name VARCHAR ( 50 );
DECLARE
var_org_name VARCHAR ( 50 );
DECLARE
var_expect_id INT;
DECLARE
var_expect_name VARCHAR ( 30 );
DECLARE
var_intestine_num INT;
DECLARE
var_stomach_num INT;
DECLARE
var_total_num INT;
DECLARE
var_years VARCHAR ( 4 );
DECLARE
var_months VARCHAR ( 2 );
DECLARE
intestine_view_cursor CURSOR FOR SELECT
mcb.id AS mcb_id,
mcb.`name` AS mcb_name,
org.`name` AS org_name,
ept.username AS expect_name,
ept.id AS expect_id,
c.m_id AS m_id,
DATE_FORMAT( c.create_time, '%Y' ) AS years,
DATE_FORMAT( c.create_time, '%c' ) AS months,
COUNT( c.inspection_subjects ) AS intestine_num
FROM
`case` c,
organization org,
medical_conjoined_body mcb,
expect ept,
checklist cl,
endoscopicreport er
WHERE
c.inspection_subjects = '小肠胶囊'
AND c.imcd_id = mcb.id
AND org.id = c.inspection_organization_id
AND c.expert_id = ept.id
AND cl.case_id = c.id
AND er.check_no = cl.check_no
AND er.audit_status = 2
AND (
c.m_id =
IF
( IF ( sysType NOT IN ( 0, 2, 3 ), TRUE, FALSE ), mId, c.m_id )
OR c.m_id =
IF
( IF ( sysType NOT IN ( 0, 2, 3 ), TRUE, FALSE ), NULL, c.m_id )
)
AND mcb.id =
IF
( IF ( sysType NOT IN ( 0, 1, 2 ), TRUE, FALSE ), mcbId, mcb.id )
GROUP BY
DATE_FORMAT( c.create_time, '%Y' ),
DATE_FORMAT( c.create_time, '%c' ),
ept.username;
DECLARE
stomach_view_cursor CURSOR FOR SELECT
mcb.id AS mcb_id,
mcb.NAME AS mcb_name,
org.`name` AS org_name,
ept.username AS expect_name,
ept.id AS expect_id,
c.m_id AS m_id,
DATE_FORMAT( c.create_time, '%Y' ) AS years,
DATE_FORMAT( c.create_time, '%c' ) AS months,
COUNT( c.inspection_subjects ) AS stomach_num
FROM
`case` c,
organization org,
medical_conjoined_body mcb,
expect ept,
checklist cl,
endoscopicreport er
WHERE
c.inspection_subjects = '胃胶囊'
AND c.imcd_id = mcb.id
AND org.id = c.inspection_organization_id
AND c.expert_id = ept.id
AND cl.case_id = c.id
AND er.check_no = cl.check_no
AND er.audit_status = 2
GROUP BY
DATE_FORMAT( c.create_time, '%Y' ),
DATE_FORMAT( c.create_time, '%c' ),
ept.username;-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
DECLARE
CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
DELETE
FROM
checklistCountOfReadorder;
OPEN intestine_view_cursor;
IF
done = 1 THEN
CLOSE intestine_view_cursor;
ELSEIF done = 0 THEN
REPEAT
FETCH intestine_view_cursor INTO var_mcb_id,
var_mcb_name,
var_org_name,
var_expect_name,
var_expect_id,
var_m_id,
var_years,
var_months,
var_intestine_num;
INSERT IGNORE INTO checklistCountOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, years, months, intestine_num, stomach_num, total_num )
VALUES
( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_months, var_intestine_num, 0, var_intestine_num );
UNTIL done
END REPEAT;
END IF;
CLOSE intestine_view_cursor;
SET done = 0;-- OPEN stomach_view_cursor;
--
-- REPEAT
-- FETCH stomach_view_cursor INTO var_mcb_id,var_mcb_name,var_org_name,var_expect_name,var_expect_id,var_m_id,var_years,var_months,var_stomach_num;
--
-- INSERT INTO checklistCountOfReadorder(mcb_id,mcb_name,org_name,expect_name,expect_id,m_id,years,months,intestine_num,stomach_num,total_num) VALUES(var_mcb_id,var_mcb_name,var_org_name,var_expect_name,var_expect_id,var_m_id,var_years,var_months,0,var_stomach_num,var_stomach_num) ON DUPLICATE KEY UPDATE stomach_num=var_stomach_num,total_num=intestine_num+var_stomach_num;
--
-- UNTIL done END REPEAT;
--
-- CLOSE stomach_view_cursor;
SET done = 0;
END
- 第三个
CREATE DEFINER = `root` @`%` PROCEDURE `statics_proce_year_month_day_readorder` ( ) BEGIN
DECLARE
done BOOLEAN DEFAULT 0;
DECLARE
var_mcb_id INT;
DECLARE
var_m_id INT;
DECLARE
var_mcb_name VARCHAR ( 50 );
DECLARE
var_org_name VARCHAR ( 50 );
DECLARE
var_expect_id INT;
DECLARE
var_expect_name VARCHAR ( 30 );
DECLARE
var_intestine_num INT;
DECLARE
var_stomach_num INT;
DECLARE
var_total_num INT;
DECLARE
var_create_time DATETIME;-- 1、创建游标完成基本数据的统计（胃胶囊v1及小肠胶囊v2）
DECLARE
intestine_view_cursor CURSOR FOR SELECT
mcb.id AS mcb_id,
mcb.NAME AS mcb_name,
org.`name` AS org_name,
ept.username AS expect_name,
ept.id AS expect_id,
c.m_id AS m_id,
DATE_FORMAT( c.create_time, '%Y-%c-%d' ) AS create_time,
COUNT( c.inspection_subjects ) AS intestine_num
FROM
`case` c,
organization org,
medical_conjoined_body mcb,
expect ept,
checklist cl,
endoscopicreport er
WHERE
c.inspection_subjects = '小肠胶囊'
AND c.imcd_id = mcb.id
AND org.id = c.inspection_organization_id
AND c.expert_id = ept.id
AND cl.case_id = c.id
AND er.check_no = cl.check_no
AND er.audit_status = 2
GROUP BY
DATE_FORMAT( c.create_time, '%Y%c%d' ),
ept.username,
mcb.`name`;
DECLARE
stomach_view_cursor CURSOR FOR SELECT
mcb.id AS mcb_id,
mcb.NAME AS mcb_name,
org.`name` AS org_name,
ept.username AS expect_name,
ept.id AS expect_id,
c.m_id AS m_id,
DATE_FORMAT( c.create_time, '%Y-%c-%d' ) AS create_time,
COUNT( c.inspection_subjects ) AS stomach_num
FROM
`case` c,
organization org,
medical_conjoined_body mcb,
expect ept,
checklist cl,
endoscopicreport er
WHERE
c.inspection_subjects = '胃胶囊'
AND c.imcd_id = mcb.id
AND org.id = c.inspection_organization_id
AND c.expert_id = ept.id
AND cl.case_id = c.id
AND er.check_no = cl.check_no
AND er.audit_status = 2
GROUP BY
DATE_FORMAT( c.create_time, '%Y%c%d' ),
ept.username,
mcb.`name`;
DECLARE
CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1;-- 2、任选一个游标（假定为v1:胃胶囊）中的数据插入到完整表t1信息（页面可直接查询并呈现的信息）：则小肠胶囊的数量设置为0，总数量total_num设置为胃胶囊的值
OPEN intestine_view_cursor;
REPEAT
FETCH intestine_view_cursor INTO var_mcb_id,
var_mcb_name,
var_org_name,
var_expect_name,
var_expect_id,
var_m_id,
var_create_time,
var_intestine_num;
INSERT IGNORE INTO checklistCountWithYearMonthDayOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, create_time, intestine_num, stomach_num, total_num )
VALUES
( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_create_time, var_intestine_num, 0, var_intestine_num );
UNTIL done
END REPEAT;
CLOSE intestine_view_cursor;
SET done = 0;-- 3、取另外一个游标（根据2中的假设则为v2：小肠胶囊）中的数据 插入到完整表t1中:
-- 采用insert...on duplicate key update statement:
-- a、如果联合唯一键（mcb_id+org_name+expect_id+year+months）重复则更新该条记录，将intestine_num字段的值更新到表t1中
-- 同时，将stomach_num中值和intestine_num的值相加然后传入到total_num字段中去。
-- b、如果联合唯一键不重复则插入数据，同时将stomach_num设置为0，total_num设置为intestine_num的值
OPEN stomach_view_cursor;
REPEAT
FETCH stomach_view_cursor INTO var_mcb_id,
var_mcb_name,
var_org_name,
var_expect_name,
var_expect_id,
var_m_id,
var_create_time,
var_stomach_num;
INSERT INTO checklistCountWithYearMonthDayOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, create_time, intestine_num, stomach_num, total_num )
VALUES
( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_create_time, 0, var_stomach_num, var_stomach_num )
ON DUPLICATE KEY UPDATE stomach_num = var_stomach_num,
total_num = intestine_num + var_stomach_num;
UNTIL done
END REPEAT;
CLOSE stomach_view_cursor;
SET done = 0;
END
结语
虽然用SQL实现的代码量也比较大，但是SQL的逻辑更简单、可读性更高，并且使用Java实现也会写不少的SQL语句，所以，综合来讲使用SQL实现更加容易。
6254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
