sql高级实践
sql高级实践
情景还原：
在企业中心中，开通岗位风控的企业一共有13个，要在数据大屏中统计这13个企业的单元，事件，管控措施，任务数。
难点解析：device表，risk_unit表，risk_event表，risk_measure 只有device表有company_id。重点在于13个企业不是每个企业都有从装置往下的一个完整的数数据树的。所以有的企业必然查不出来
也就是如果IPage
如果页大小是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
<select id="getUserDatas" resultType="com.shsajt.common.dto.UserDataWorkDTO">
SELECT temp.user_id,
ifnull(tempwap.count,0) as count
from
(
<if test ="userIds != null and userIds.size() >0">
<foreach collection="userIds" index="index" item="item" open="(" separator="union" close=")">
select #{item} as user_id
</foreach>
</if>
)
temp left join
(
select wap.user_id, count(*) as count
from
work_and_person wap
left join contract_work cw on wap.work_id = cw.id
left join closed_loop cl on cw.id = cl.work_id
<where>
wap.deleted = 0 and cw.deleted = 0 and cw.work_state_id = 3
and ( (cw.work_type_id = 8 and cl.looped = 1) or (cw.work_type_id != 8 and cl.looped is null) )
<if test ="userIds != null and userIds.size() >0">
and wap.user_id in
<foreach collection='userIds' item='item' index='index' open='(' separator=',' close=')'>
#{item}
</foreach>
</if>
</where>
GROUP BY wap.user_id
)
tempwap on temp.user_id = tempwap.user_id order by count desc
</select>
以传入的 List
