sql高级实践

sql高级实践

情景还原：

在企业中心中，开通岗位风控的企业一共有13个，要在数据大屏中统计这13个企业的单元，事件，管控措施，任务数。

难点解析：device表，risk_unit表，risk_event表，risk_measure 只有device表有company_id。重点在于13个企业不是每个企业都有从装置往下的一个完整的数数据树的。所以有的企业必然查不出来

也就是如果IPage numPage = dataviewMapper.controlListPage(page, companyIds); // 不含有企业名称的list数据

如果页大小是20（最低13），查出来的少于13个，但是这是不被允许的吗，分页大小必须是13个（开通岗位风控的企业个数），铁定不动的。所以要想让页大小对的上，就需要在sql中认为的构造好sql。

@Override
	public IPage<CompanyControlList> controlListPage(CompanyPageVO vo, Integer govId) {
		Integer currentPage = vo.getCurrentPage();
		Integer pageSize = vo.getPageSize();
		Integer companyId = vo.getCompanyId();	
		List<Integer> companyIds = this.getPageCompanyIds(govId, companyId);
		
		IPage<ControlListNum> page = new Page<>(currentPage, pageSize);
		IPage<ControlListNum> numPage = dataviewMapper.controlListPage(page, companyIds);	// 不含有企业名称的list数据
	
		IPage<CompanyControlList> listPage = new Page<>(currentPage, pageSize);
		listPage.setTotal(numPage.getTotal());
		List<ControlListNum> nums = numPage.getRecords();
		if (CollectionUtils.isEmpty(nums)) {
			return listPage;
		}
		
		Set<Integer> existCompanyIds = nums.stream().map(ControlListNum::getCompanyId).collect(Collectors.toSet());
		Map<Integer, String> idNameMap = dubboCompanyService.getCompanyNameMap(new ArrayList<>(existCompanyIds));
		
		List<CompanyControlList> companyControlLists = new ArrayList<>();					// 构造含有企业名称的list数据
		CompanyControlList controlList;
		for (ControlListNum num : nums) {
			controlList = new CompanyControlList();
			controlList.setCompanyName(idNameMap.get(num.getCompanyId()));
			controlList.setUnitNum(num.getUnitNum());
			controlList.setEventNum(num.getEventNum());
			controlList.setMeasureNum(num.getMeasureNum());
			controlList.setTaskNum(num.getMeasureNum());
			companyControlLists.add(controlList);
		}
		listPage.setRecords(companyControlLists);
		return listPage;
	}

sql解析

首先构造好一个13个企业的a表。

以这个表为基本表连接 同样道理组件起来的b表：

b表少了几个企业，但是没关系，因为a表左连接b表后，会将少的企业补齐：

select a.company_id, 
 	ifnull(b.unit_num,0) as unit_num, 
 	ifnull(b.event_num,0)as event_num , 
 	ifnull(b.measure_num,0)as measure_num from
	(
		  select 845 as company_id union
		  select 813 as company_id union
		  select 207 as company_id union
		  select 95 as company_id union
		  select 122 as company_id union
		  select 288 as company_id union
		  select 164 as company_id union
		  select 131 as company_id union
		  select 195 as company_id union
		  select 87 as company_id union
		  select 151 as company_id union
		  select 287 as company_id union
		  select 285 as company_id union
		  select 17 as company_id 	 
 	)a
 left join(
	 select 
	   d.company_id,
	   count(distinct ru.id) as unit_num,
	   count(distinct re.id) as event_num,
	   count(distinct rm.id) as measure_num
	  from device d 
	  left join risk_unit ru on
	   d.id = ru.device_id
	  left join risk_event re on
	   re.risk_unit_id = ru.id
	  left join risk_measure rm on
	   rm.risk_event_id = re.id
	  where
	    d.deleted = 0
	   and ru.deleted = 0
	   and re.deleted = 0
	   and rm.deleted = 0
	    and d.company_id in
	    (845, 813, 207, 95, 122, 288, 164, 131, 195, 87, 151, 287, 285, 17)
	  group by d.company_id
 
 ) b  on a.company_id = b.company_id
group by a.company_id