laravel仅限制2行加入 [英] laravel join with only limiting 2 row

问题描述

所以我有这种查询，我会像这样以最大日期/期间进行分组

so i having this kind of query where i do grouping with max date/period like this

$table_data = App\salesreport::join(DB::RAW('(SELECT company_id, MAX(period) AS max_period FROM salesreport GROUP BY company_id ) latest_report'),function($join){
            $join->on('salesreport.company_id','=','latest_report.company_id');
            $join->on('salesreport.periods','=','latest_report.max_periods');
    })->get()

，它工作得很好，它可以将所有公司分组为一个，然后仅显示最新的一个MAX(period).然后，我想进行更多调整，如果我不仅要展示每个公司的最新公司，还要展示最新的两家公司，那又如何呢?因此每个公司将返回2个报告，这是所有报告中的最新报告.

and it work just fine, it can group all company into one and then showing only the latest one MAX(period). And then i want to tweak it more, how about if i want to show not only the latest one, but also the latest two of each company? so each company will returning 2 report that is the latest of all report.

然后我想也许添加LIMIT 2会使它起作用，所以我这样说

and then i think maybe adding LIMIT 2 will make it works so i put it like this

$table_data = App\salesreport::join(DB::RAW('(SELECT company_id, MAX(period) AS max_period FROM salesreport GROUP BY company_id ) latest_report'),function($join){
            $join->on('salesreport.company_id','=','latest_report.company_id');
            $join->on('salesreport.periods','<=','latest_report.max_periods');
            $join->limit(2)
    })->get()

但是它没有任何作用，仅显示周期为< = max_periods的所有公司的所有报告.

but it has no effect and just showing all report of all company which have period <= max_periods.

为了使事情更清楚，这是我加入的桌子

to make things more clearer here is my table that i do joining

这是我的销售报告表

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  1 | A1         |   500 | 2016-07-12 |
|  2 | A2         |   540 | 2017-01-21 |
|  3 | A1         |   440 | 2017-01-19 |
|  4 | A1         |   440 | 2018-01-19 |
|  5 | A2         |   330 | 2016-01-12 |
|  6 | A2         |   333 | 2018-01-22 |
+----+------------+-------+------------+

然后使用那里的第一个查询，然后我会得到这种表

and then using the first query up there then i will get this kind of table

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  4 | A1         |   440 | 2018-01-19 |
|  6 | A2         |   333 | 2018-01-22 |
+----+------------+-------+------------+

所以我想要的是获得这种桌子

so what i want is to get this kind of table

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  4 | A1         |   440 | 2018-01-19 |
|  3 | A1         |   440 | 2017-01-19 |
|  6 | A2         |   333 | 2018-01-22 |
|  2 | A2         |   540 | 2017-01-21 |
+----+------------+-------+------------+

那么每个公司的最新2个..可能吗?只需一个请求?

so the latest 2 of all for each company.. is it possible? with only one request?

推荐答案

使用此技巧:

App\salesreport::join(DB::RAW('(SELECT company_id, GROUP_CONCAT(periods ORDER BY periods DESC) grouped_periods FROM salesreport GROUP BY company_id ) latest_report'),function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->whereBetween(DB::raw('FIND_IN_SET(`salesreport`.`periods`, `latest_report`.`grouped_periods`)'), [1, 2]);
    })->get();

这篇关于laravel仅限制2行加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！