526互联

thinkphp5 使用group查询最新的一条记录

发布时间 2023-10-24 17:58:42作者: 亚索会代码

thinkphp5示例：

//子查询主要的在MAX(create_time)
$subQuery = Db::table('fa_crm_record')
                        ->field('customer_id,MAX(create_time) AS create_time')
                        ->group('customer_id')
                        ->buildSql();
                        
//主查询主要的在"r.create_time=record.create_time"
$list = (new Customer)
            ->with(['record'])
            ->order("record.create_time desc")
            ->where($where)
            ->join("$subQuery r","r.create_time=record.create_time")
            ->paginate($limit);

//或者$this->model = new Record();
$list = $this->model
            ->with(['customer'])
            ->order("record.create_time desc")
            ->where($where)
            ->join("$subQuery r","r.create_time=record.create_time")
            ->paginate($limit);

 

原SQL查询，子查询里max是关键，主要是获取最新的时间戳然后赋值给新的create_time

#原数据
SELECT id,customer_id,content,create_time FROM fa_crm_record ORDER BY create_time desc

如果你直接使用group查询的话会这样

SELECT id,customer_id,content,create_time FROM fa_crm_record GROUP BY customer_id ORDER BY create_time desc

明显customer_id=1725，1726不是最新的信息,最新的信息ID是47，45才对

 

现在用子查询把最新的时间取出来

SELECT id,content,customer_id, MAX(create_time) AS create_time
    FROM fa_crm_record GROUP BY customer_id ORDER BY create_time desc

 

咋一看以为跟上面的效果差不错，但是你看create_time的时间是不是ID47和ID45的时间戳来着，这样就拿到了最新的时间了，不用管这个返回的ID

 

拿到了这个时间搓之后，就可以来查询了

SELECT t1.id,t1.content,t1.customer_id,t1.create_time
FROM fa_crm_record t1
INNER JOIN (
    SELECT customer_id, MAX(create_time) AS create_time
    FROM fa_crm_record
    GROUP BY customer_id
) t2 ON t1.create_time = t2.create_time ORDER BY create_time desc;

回过去第一个查询sql跟这个查询对比，是不是拿到最新的查询了