sqlserver对字段相同的表进行合并查询

sqlserver对字段相同的表进行合并查询

Ezra
2023-03-20 / 0 评论 / 106 阅读 / 正在检测是否收录...

如果有一个项目每个月分一次表,字段是相同的,想进行正常查询用以下方法:

/**
     * 分表后根据合并分表查询,
     * tp的union方法好像对sqlserver不好使
     */
    public function get_union_index($stime,$etime,$eqid,$field='*'){
        //首先先查出日期所对应的月份,确认要查的表
        $sqls = self::join_instant_table($stime, $etime,$field);
        $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime','desc')->where(['EQTime' => ['between', [$stime, $etime]]]);
        $sql=$sql->field($field);
        $sql=$sql->select();
        return $sql;
    }
    /**
     * 分页版
     */
    public function get_union_page($stime,$etime,$eqid,$field='*'){
        $sqls = self::join_instant_table($stime, $etime,$field);
        $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime','desc')->where(['EQTime' => ['between', [$stime, $etime]]]);
        $sql=$sql->field($field);
        $sql=$sql->paginate();
        return $sql;
    }
    /**
     * 第一条或最后一条
     */
    public function get_union_first($stime,$etime,$eqid,$field='*',$order='desc'){
        $sqls = self::join_instant_table($stime, $etime,$field);
        $sql=Db::table($sqls)->where('EQID',$eqid)->order('EQTime',$order)->where(['EQTime' => ['between', [$stime, $etime]]]);
        $sql=$sql->field($field);
        $sql=$sql->find();
        return $sql;
    }

    public function join_instant_table($begin, $end, $field = '*')
    {
        $tables = self::get_instant_tables($begin, $end);
        $sqls   = [];
        foreach ($tables as $table) {
            //判断表是否存在
            try {
                db::name($table)->find();
                $sql = sprintf("select {$field} from value_%s", $table);
                $sqls[] = $sql;
            } catch (\Throwable $e) {
                continue;
            }
        }
        if ($sqls) {
            return '(' . implode(' union all ', $sqls) . ') i';
        }
        return '';
    }

    /**
     * @param begin string 开始时间
     * @param end string 结束时间
     * @return:
     */
    public function get_instant_tables($begin,$end)
    {
        $table_basis = 'meter_instant';
        $initial = strtotime('2022-12-01');
        $begin = !empty($begin) ? strtotime($begin) : $initial;
        $end = !empty($end) ? strtotime($end) : time();
        if ($end > time()) $end = time();
        // if ($end < $initial) return [$table_basis.'_202212'];

        $end3 = strtotime(date('Y-m-01'));//2
        // if ($begin >= $end3) return [$table_basis];
        $tables = [];
        while ($begin <= $end) {
            if ($begin < $initial) {
                $tables[] = $table_basis.'_202212';
            }
             else if ($begin >= $end3) {
                $tables[] = $table_basis;
            } 
            else {
                $tables[] = $table_basis.'_'.date('Y',$begin).date('m',$begin);
            }
            $begin = strtotime('+1 month '.date('Y-m-01',$begin));
        }
        return array_values(array_unique($tables));
    }
0

评论 (0)

取消