String sql = "select a1.*,a2.componentCount,a4.serviceCount,x.name groupName " +
" from (select * from " + TABLE_NAME + " where tenant_id = ? and deleted = 0 and struct_id = ? and type = 0 and (instr(id,?)>0 OR instr(name,?)>0) order by id desc limit " + pageIndex * pageSize + "," +
pageSize + " ) a1 " +
"left join thing_group x on a1.group_id=x.id" +
" left join " +
" (select device_id,count(component_id) componentCount from " + ThingDeviceComponentDao.TABLE_NAME + " where deleted = 0 group by device_id ) a2 " +
" on a1.id = a2.device_id " +
" left join " +
" (select tt3.device_id,sum(serviceNumber) serviceCount from " + ThingDeviceComponentDao.TABLE_NAME + " tt3 " +
" left join " +
" (select count(tt1.id) serviceNumber,tt2.component_id from " + ThingEventDao.TABLE_NAME + " tt1 " +
" right join " +
ThingDataStreamDao.TABLE_NAME + " tt2 on tt2.tenant_id = ? and tt2.deleted=0 and tt2.id =tt1.stream_id where tt1.tenant_id = ? and tt1.deleted = 0 group by tt2.component_id) tt4 " +
" on tt4.component_id=tt3.component_id group by tt3.device_id) a4 on a4.device_id = a1.id ";
其中thing_device 82560
thing_group 1938
thing_device_component_ref 240671
thing_event 813
thing_data_stream 4894
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/92371.html
標籤：MySQL