历史数据高效跨表分页

雨点打透心脏的1/2处 2021-12-11 04:31 272阅读 0赞

由于历史数据会随着时间的推移越来越多，因此查询的速度会越来越慢，所以大多数人都会想到分表。

而百度跨表分页后，会发现主要有两种想法。

一种是将可能需要查询的表内所有的历史数据查出来，然后在内存中进行时间排序

还有一种的主要思路是查询时建立临时表，将数据插入临时表中，然后从临时表内分页查询。

这两种思路都比较。。。。慢而且浪费内存和cpu。

因此我想了一种比较优秀的方式，相信大家都会夸我。。。。

直入主题：

注意前提条件   按时间分表，而且查询结果只能有按时间排序（倒序）的方式

满足前提条件才可以中这种思路。

每次查询先计算最近的一个表的内的数据量，以及当前需要的数据的数量以及偏移量，如果表内数据量不够，则向前查一张表的数据量。。。依次内推。

这种做法的好处是

1.效率高，count的速度会相对快一点，比将数据查出来快很多

2.占用内存极少，仅将一页数据查询出来，内存消耗小

3.避免了大量数据的排序，极大的降低了cpu的消耗

真正做到每次只查需要的数据

分页的思路就是这样，具体的实现有n种，这里介绍我的实现方式。

主要的实现方式，利用mybatis的拦截器和java的反射，将一个简单的普通sql 在运行时动态的拼接成跨表分页的高级sql

极大的降低了开发的复杂度。

添加配置（这里之前使用了一个分页插件，也是使用拦截器的，因此配置在一起）

![20190710140651367.png][]

import com...entity.page.Page;
    import com...entity.page.TablePage;
    import com...util.ReflectHelper;
    import com...util.TablePageHelper;
    import com...util.Tools;
    import org.apache.ibatis.executor.ErrorContext;
    import org.apache.ibatis.executor.ExecutorException;
    import org.apache.ibatis.executor.statement.BaseStatementHandler;
    import org.apache.ibatis.executor.statement.RoutingStatementHandler;
    import org.apache.ibatis.executor.statement.StatementHandler;
    import org.apache.ibatis.mapping.BoundSql;
    import org.apache.ibatis.mapping.MappedStatement;
    import org.apache.ibatis.mapping.ParameterMapping;
    import org.apache.ibatis.mapping.ParameterMode;
    import org.apache.ibatis.plugin.*;
    import org.apache.ibatis.reflection.MetaObject;
    import org.apache.ibatis.reflection.property.PropertyTokenizer;
    import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
    import org.apache.ibatis.session.Configuration;
    import org.apache.ibatis.type.TypeHandler;
    import org.apache.ibatis.type.TypeHandlerRegistry;
    import org.springframework.util.StringUtils;
    
    import javax.xml.bind.PropertyException;
    import java.lang.reflect.Field;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.*;
    
    /**
     * 类名称：PagePlugin.java
     * 类描述：
     *
     * @author Analog TY
     * 作者单位：
     * 创建时间：2014年7月1日
     * @version 1.0
     * @modify TY 2019-03-15
     * @modify TY 2019-04-25
     */
    @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
    public class PagePlugin implements Interceptor {
    
        /**
         * 数据库方言
         */
        private static String dialect = "";
        /**
         * mapper.xml中需要拦截的ID(正则匹配)
         */
        private static String pageSqlId = "";
    
        private static String tablePageSqlId = ".*tablePage.*";
    
        @Override
        public Object intercept(Invocation ivk) throws Throwable {
            if (ivk.getTarget() instanceof RoutingStatementHandler) {
                RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk.getTarget();
                BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate");
                MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement");
    
                //拦截需要分页的SQL
                if (mappedStatement.getId().matches(pageSqlId)) {
                    BoundSql boundSql = delegate.getBoundSql();
    
                    //分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空
                    Object parameterObject = boundSql.getParameterObject();
                    if (parameterObject == null) {
                        throw new NullPointerException("parameterObject尚未实例化！");
                    } else {
                        Connection connection = (Connection) ivk.getArgs()[0];
                        String sql = boundSql.getSql();
                        //记录统计 == oracle 加 as 报错(SQL command not properly ended)
                        String countSql = "select count(0) from (" + sql + ")  tmp_count";
                        PreparedStatement countStmt = connection.prepareStatement(countSql);
                        BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
                        setParameters(countStmt, mappedStatement, countBS, parameterObject);
                        ResultSet rs = countStmt.executeQuery();
                        int count = 0;
                        if (rs.next()) {
                            count = rs.getInt(1);
                        }
                        rs.close();
                        countStmt.close();
                        Page page;
                        //参数就是Page实体
                        if (parameterObject instanceof Page) {
                            page = (Page) parameterObject;
                            page.setEntityOrField(true);
                            page.setTotalResult(count);
                        } else {    //参数为某个实体，该实体拥有Page属性
                            Field pageField = ReflectHelper.getFieldByFieldName(parameterObject, "page");
                            if (pageField != null) {
                                page = (Page) ReflectHelper.getValueByFieldName(parameterObject, "page");
                                if (page == null) {
                                    page = new Page();
                                }
                                page.setEntityOrField(false);
                                page.setTotalResult(count);
                                //通过反射，对实体对象设置分页对象
                                ReflectHelper.setValueByFieldName(parameterObject, "page", page);
                            } else {
                                throw new NoSuchFieldException(parameterObject.getClass().getName() + "不存在 page 属性！");
                            }
                        }
                        String pageSql = generatePageSql(sql, page);
                        //将分页sql语句反射回BoundSql.
                        ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql);
                    }
                } else if (mappedStatement.getId().matches(tablePageSqlId)) {
                    BoundSql boundSql = delegate.getBoundSql();
    
                    //分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空
                    Object parameterObject = boundSql.getParameterObject();
                    if (parameterObject == null) {
                        throw new NullPointerException("parameterObject尚未实例化！");
                    } else {
                        Page page;
                        //参数就是Page实体
                        if (parameterObject instanceof Page) {
                            page = (Page) parameterObject;
                            page.setEntityOrField(true);
                        } else {
                            //参数为某个实体，该实体拥有Page属性
                            Field pageField = ReflectHelper.getFieldByFieldName(parameterObject, "page");
                            if (pageField != null) {
                                page = (Page) ReflectHelper.getValueByFieldName(parameterObject, "page");
                                if (page == null) {
                                    page = new Page();
                                }
                                page.setEntityOrField(false);
                                //通过反射，对实体对象设置分页对象
                                ReflectHelper.setValueByFieldName(parameterObject, "page", page);
                            } else {
                                throw new NoSuchFieldException(parameterObject.getClass().getName() + "不存在 page 属性！");
                            }
                        }
    
                        Connection connection = (Connection) ivk.getArgs()[0];
                        String sql = boundSql.getSql();
                        //记录统计 == oracle 加 as 报错(SQL command not properly ended)
                        String[] sqlArr = sql.split(TablePage.getTableName());
                        TablePage tablePage = page.getTablePage();
                        List<String> tables;
                        if (tablePage != null) {
                            tables = TablePageHelper.getTables(tablePage.getTableHead(), tablePage.getStartYear(), tablePage.getStartMonth(), tablePage.getEndYear(), tablePage.getEndMonth());
                        } else {
                            throw new NoSuchFieldException("page 对象中 tablePage属性 不存在！");
                        }
    
                        //将生成的table拿到数据库中验证
                        String dataBaseTablesSql = "SHOW TABLES LIKE \"" + tablePage.getTableHead() + "%\"";
                        PreparedStatement dataBaseTablesStmt = connection.prepareStatement(dataBaseTablesSql);
                        ResultSet dataBaseTablesResultSet = dataBaseTablesStmt.executeQuery();
                        Set<String> dataBaseTables = new HashSet<>();
                        while (dataBaseTablesResultSet.next()) {
                            dataBaseTables.add(dataBaseTablesResultSet.getString(1));
                        }
                        dataBaseTablesResultSet.close();
                        dataBaseTablesStmt.close();
    
                        //求交集，获取库中的table
                        tables.retainAll(dataBaseTables);
                        //倒序，接近的日期在前面
                        Collections.reverse(tables);
    
                        List<Map<String, Object>> tablesAndNumList = new ArrayList<>(tables.size());
                        int count = 0;
                        for (String table : tables) {
                            int tempCount = 0;
                            String tempSql = "";
                            for (int i = 0; i < sqlArr.length; i += 2) {
                                tempSql += sqlArr[i] + table + sqlArr[i + 1];
                            }
                            String countSql = "select count(0) from (" + tempSql + ")  tmp_count";
                            PreparedStatement countStmt = connection.prepareStatement(countSql);
                            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject);
                            setParameters(countStmt, mappedStatement, countBS, parameterObject);
                            ResultSet rs = countStmt.executeQuery();
                            if (rs.next()) {
                                tempCount = rs.getInt(1);
                                count += tempCount;
                            }
                            rs.close();
                            countStmt.close();
    
                            Map<String, Object> tempTableAndNum = new HashMap<>(2);
                            tempTableAndNum.put("table", table);
                            tempTableAndNum.put("count", tempCount);
                            tablesAndNumList.add(tempTableAndNum);
                        }
                        page.setTotalResult(count);
    
                        //获取原始参数
                        List<ParameterMapping> param = boundSql.getParameterMappings();
    
                        //获取sql和参数
                        Map<String, Object> result = generateTablePageSqlAndParam(sqlArr, tablesAndNumList, page, param);
                        //将分页sql语句反射回BoundSql.
                        ReflectHelper.setValueByFieldName(boundSql, "sql", result.get("sql"));
                        //将参数反射回BoundSql.
                        ReflectHelper.setValueByFieldName(boundSql, "parameterMappings", result.get("param"));
                    }
                }
            }
            return ivk.proceed();
        }
    
    
        /**
         * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
         *
         * @param ps              preparedStatement
         * @param mappedStatement mappedStatement
         * @param boundSql        boundSql
         * @param parameterObject parameterObject
         * @throws SQLException SQLException
         */
        private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
            ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            if (parameterMappings != null) {
                Configuration configuration = mappedStatement.getConfiguration();
                TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
                MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
                for (int i = 0; i < parameterMappings.size(); i++) {
                    ParameterMapping parameterMapping = parameterMappings.get(i);
                    if (parameterMapping.getMode() != ParameterMode.OUT) {
                        Object value;
                        String propertyName = parameterMapping.getProperty();
                        PropertyTokenizer prop = new PropertyTokenizer(propertyName);
                        if (parameterObject == null) {
                            value = null;
                        } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                            value = parameterObject;
                        } else if (boundSql.hasAdditionalParameter(propertyName)) {
                            value = boundSql.getAdditionalParameter(propertyName);
                        } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) {
                            value = boundSql.getAdditionalParameter(prop.getName());
                            if (value != null) {
                                value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));
                            }
                        } else {
                            value = metaObject == null ? null : metaObject.getValue(propertyName);
                        }
                        TypeHandler typeHandler = parameterMapping.getTypeHandler();
                        if (typeHandler == null) {
                            throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
                        }
                        typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                    }
                }
            }
        }
    
        /**
         * 根据数据库方言，生成特定的分页sql
         *
         * @param sql  sql
         * @param page page
         * @return String
         */
        private String generatePageSql(String sql, Page page) {
            if (page != null && Tools.notEmpty(dialect)) {
                StringBuffer pageSql = new StringBuffer();
                if ("mysql".equals(dialect)) {
                    pageSql.append(sql);
                    if (page.isIstype()) {
                        pageSql.append(" limit " + page.getiDisplayStart() + "," + page.getiDisplayLength());
                    } else {
                        pageSql.append(" limit " + page.getCurrentResult() + "," + page.getShowCount());
                    }
    
                } else if ("oracle".equals(dialect)) {
                    pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
                    pageSql.append(sql);
                    pageSql.append(") tmp_tb where ROWNUM<=");
                    pageSql.append(page.getCurrentResult() + page.getShowCount());
                    pageSql.append(") where row_id>");
                    pageSql.append(page.getCurrentResult());
                }
                return pageSql.toString();
            } else {
                return sql;
            }
        }
    
        /**
         * 根据数据库方言，生成特定的分页sql(仅支持mysql)
         *
         * @param sqlArr       sql 根据tableName拆分的前后断
         * @param tablesAndNum tablesAndNum 表和表的大小
         * @param page         page
         * @return String
         */
        private Map<String, Object> generateTablePageSqlAndParam(String[] sqlArr, List<Map<String, Object>> tablesAndNum, Page page, List<ParameterMapping> param) {
            Map<String, Object> returnMap = new HashMap<>(2);
            if (page != null && "mysql".equals(dialect)) {
                int beginCount = (page.getCurrentPage() - 1) * page.getShowCount();
                List<Map<String, Object>> tableAndLimit = new ArrayList<>();
                int showCount = page.getShowCount();
    
                int tempBeginCount = beginCount;
                for (int i = 0; i < tablesAndNum.size(); i++) {
                    int countN = Integer.parseInt(tablesAndNum.get(i).get("count").toString());
                    tempBeginCount -= countN;
                    if (tempBeginCount < 0) {
                        //当前表的开始数量
                        tempBeginCount = tempBeginCount + countN;
                        //当前表能够取出的剩余数量
                        int tempCanShowCount = countN - tempBeginCount;
                        //当前表不够的数量（负数表示足够）
                        int tempHasShowCount = showCount - tempCanShowCount;
                        //当前表展示的数量
                        int tempShowCount = Math.min(showCount, tempCanShowCount);
    
                        Map<String, Object> tempTable = new HashMap<>(3);
                        tempTable.put("table", tablesAndNum.get(i).get("table").toString());
                        tempTable.put("begin", tempBeginCount);
                        tempTable.put("showCount", tempShowCount);
                        tableAndLimit.add(tempTable);
    
                        for (int j = i + 1; j < tablesAndNum.size() && tempHasShowCount > 0; j++) {
                            countN = Integer.parseInt(tablesAndNum.get(j).get("count").toString());
                            //当前表的开始数量
                            tempBeginCount = 0;
                            //当前表能够取出的剩余数量
                            tempCanShowCount = countN - tempBeginCount;
                            //当前表展示的数量
                            tempShowCount = Math.min(tempHasShowCount, tempCanShowCount);
                            //当前表不够的数量（负数表示足够）
                            tempHasShowCount -= tempCanShowCount;
    
                            tempTable = new HashMap<>(3);
                            tempTable.put("table", tablesAndNum.get(j).get("table").toString());
                            tempTable.put("begin", tempBeginCount);
                            tempTable.put("showCount", tempShowCount);
                            tableAndLimit.add(tempTable);
                        }
                        break;
                    }
                }
    
                String tempSql = "";
                List<ParameterMapping> returnParam = new ArrayList<>();
                for (Map<String, Object> tableMap : tableAndLimit) {
                    tempSql += "(";
                    String table = tableMap.get("table").toString();
                    int thisBegin = Integer.parseInt(tableMap.get("begin").toString());
                    int thisShowCount = Integer.parseInt(tableMap.get("showCount").toString());
    
                    //防止sql中多次出现表名
                    for (int i = 0; i < sqlArr.length; i += 2) {
                        tempSql += sqlArr[i] + table + sqlArr[i + 1];
                    }
                    //拼接分页
                    tempSql += " limit " + thisBegin + "," + thisShowCount + ") UNION ALL ";
                    returnParam.addAll(param);
                }
                //没有数据
                int flag = tempSql.lastIndexOf("UNION ALL ");
                if (flag != -1) {
                    tempSql = tempSql.substring(0, tempSql.lastIndexOf("UNION ALL "));
                }
                //没有表(数据一定是空，所以查出空即可)
                if (StringUtils.isEmpty(tempSql)) {
                    tempSql = "SELECT 1 FROM information_schema.processlist WHERE 1 = 0 ;";
                }
                returnMap.put("sql", tempSql);
                returnMap.put("param", returnParam);
            }
            return returnMap;
        }
    
        @Override
        public Object plugin(Object arg0) {
            return Plugin.wrap(arg0, this);
        }
    
        @Override
        public void setProperties(Properties p) {
            dialect = p.getProperty("dialect");
            if (Tools.isEmpty(dialect)) {
                try {
                    throw new PropertyException("dialect property is not found!");
                } catch (PropertyException e) {
                    e.printStackTrace();
                }
            }
            pageSqlId = p.getProperty("pageSqlId");
            if (Tools.isEmpty(pageSqlId)) {
                try {
                    throw new PropertyException("pageSqlId property is not found!");
                } catch (PropertyException e) {
                    e.printStackTrace();
                }
            }
        }
    
    }

这里仅正对mysql实现了，orical没有完善，需要注意slq的拼接

而配合拦截器使用需要一个实体，跨表分页实体

/**
     * @ClassName TablePage
     * @Description 分表分页的必传参数
     * @Author TY
     * @Date 16:23 2019-04-24
     * @Version 1.0
     **/
    public class TablePage {
    
        /**
         * 用于sql中占位使用
         */
        private final static String tableName = "tempTableName";
    
        private String tableHead;
    
        private Integer startYear;
    
        private Integer startMonth;
    
        private Integer endYear;
    
        private Integer endMonth;
    
        public TablePage() {
    
        }
    
        public TablePage(String tableHead, Integer startYear, Integer startMonth, Integer endYear, Integer endMonth) {
            this.tableHead = tableHead;
            this.startYear = startYear;
            this.startMonth = startMonth;
            this.endYear = endYear;
            this.endMonth = endMonth;
        }
    
        public static String getTableName() {
            return tableName;
        }
    
        public String getTableHead() {
            return tableHead;
        }
    
        public void setTableHead(String tableHead) {
            this.tableHead = tableHead;
        }
    
        public Integer getStartYear() {
            return startYear;
        }
    
        public void setStartYear(Integer startYear) {
            this.startYear = startYear;
        }
    
        public Integer getStartMonth() {
            return startMonth;
        }
    
        public void setStartMonth(Integer startMonth) {
            this.startMonth = startMonth;
        }
    
        public Integer getEndYear() {
            return endYear;
        }
    
        public void setEndYear(Integer endYear) {
            this.endYear = endYear;
        }
    
        public Integer getEndMonth() {
            return endMonth;
        }
    
        public void setEndMonth(Integer endMonth) {
            this.endMonth = endMonth;
        }
    }

import com..service.util.Const;
    import com..service.util.PageData;
    import com..service.util.Tools;
    
    /**
     * @param <T> 数据对象的类类型
     * @author analog
     * @modify ty
     */
    public class Page<T> {
    
        /**
         * 每页显示记录数
         */
        private int showCount;
        /**
         * 总页数
         */
        private int totalPage;
        /**
         * 总记录数
         */
        private int totalResult;
        /**
         * 当前页
         */
        private int currentPage;
        /**
         * 当前记录起始索引
         */
        private int currentResult;
        /**
         * true:需要分页的地方，传入的参数就是Page实体；false:需要分页的地方，传入的参数所代表的实体拥有Page属性
         */
        private boolean entityOrField;
        /**
         * 最终页面显示的底部翻页导航，详细见：getPageStr();
         */
        private String pageStr;
        /**
         * 存放数据对象
         */
        private T t;
    
        private boolean istype;
        private PageData pd = new PageData();
    
        private int iDisplayStart;
        private int iDisplayLength;
    
        private TablePage tablePage;
    
        /**
         * 获取数据对象
         *
         * @return T
         */
        public T getDataObject() {
            return t;
        }
    
        /**
         * 设置数据对象
         *
         * @param t t
         */
        public void setDataObject(T t) {
            this.t = t;
        }
    
        public boolean isIstype() {
            return istype;
        }
    
        public void setIstype(boolean istype) {
            this.istype = istype;
        }
    
        public int getiDisplayStart() {
            return iDisplayStart;
        }
    
        public void setiDisplayStart(int iDisplayStart) {
            this.iDisplayStart = iDisplayStart;
        }
    
        public int getiDisplayLength() {
            return iDisplayLength;
        }
    
        public void setiDisplayLength(int iDisplayLength) {
            this.iDisplayLength = iDisplayLength;
        }
    
        public Page() {
            try {
                this.showCount = Integer.parseInt(Tools.readTxtFile(Const.PAGE));
            } catch (Exception e) {
                this.showCount = 15;
            }
        }
    
        public int getTotalPage() {
            if (totalResult % showCount == 0) {
                totalPage = totalResult / showCount;
            } else {
                totalPage = totalResult / showCount + 1;
            }
            return totalPage;
        }
    
        public void setTotalPage(int totalPage) {
            this.totalPage = totalPage;
        }
    
        public int getTotalResult() {
            return totalResult;
        }
    
        public void setTotalResult(int totalResult) {
            this.totalResult = totalResult;
        }
    
        public TablePage getTablePage() {
            return tablePage;
        }
    
        public void setTablePage(TablePage tablePage) {
            this.tablePage = tablePage;
        }
    
        public int getCurrentPage() {
            if (currentPage > getTotalPage()) {
                currentPage = getTotalPage();
            }
            if (currentPage <= 0) {
                currentPage = 1;
            }
            return currentPage;
        }
    
        public void setCurrentPage(int currentPage) {
            this.currentPage = currentPage;
        }
    
        public void setPageStr(String pageStr) {
            this.pageStr = pageStr;
        }
    
        public int getShowCount() {
            return showCount;
        }
    
        public void setShowCount(int showCount) {
    
            this.showCount = showCount;
        }
    
        public int getCurrentResult() {
            currentResult = (getCurrentPage() - 1) * getShowCount();
            if (currentResult < 0) {
                currentResult = 0;
            }
            return currentResult;
        }
    
        public void setCurrentResult(int currentResult) {
            this.currentResult = currentResult;
        }
    
        public boolean isEntityOrField() {
            return entityOrField;
        }
    
        public void setEntityOrField(boolean entityOrField) {
            this.entityOrField = entityOrField;
        }
    
        public PageData getPd() {
            return pd;
        }
    
        public void setPd(PageData pd) {
            this.pd = pd;
        }
    
    }

而调用就十分简单了

service中的调用，必须使用page作为参数，page中必须使用TablePage

例如：

TablePage tablePage = new TablePage(tableHead, Integer.valueOf(start[0]), Integer.valueOf(start[1]), Integer.valueOf(end[0]), Integer.valueOf(end[1]));
            page.setTablePage(tablePage);
            List<PageData> pageDataList = deviceAlarmHistoryMapper.alarmHistorytablePage(page);

mapper中需要注意的是id，id必须以\*tablePage结尾，入参必须是page，而且sql结尾不可以打封号！！！（因为要修改sql）

<select id="alarmHistorytablePage" parameterType="page" resultType="pd">
            SELECT
                T.id,
                T.device_id as deviceId,
                T.variable_id as variableId,
                T.value ,
                DATE_FORMAT(T.date,'%Y-%m-%d %H:%i:%s') as date,
                T.state,
                T.operator_id as operatorId,
              (select U.username FROM USER U WHERE U.id = T.operator_id ) AS userName
            from
              ${tablePage.tableName} T 
              LEFT JOIN group_device_relationship GDR  ON GDR.device_id = T.device_id
            where
              GDR.group_id = #{pd.groupId}
            and DATE_FORMAT(T.date,'%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{pd.startDate},'%Y-%m-%d %H:%i:%s')
            and DATE_FORMAT(T.date,'%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(#{pd.endDate},'%Y-%m-%d %H:%i:%s')
            ORDER BY T.date DESC
        </select>

整体逻辑非常清晰，使用非常方便。不过可能需要修改一些地方才可以供大家使用。

[20190710140651367.png]: /images/20211211/5e242bb7fab24a25b4ee569c65cf43d6.png