我正在尝试获取表上单个客户的最新记录。例子:
ID Customer City Amount
1 Cust001 City1 2
2 Cust001 City2 3
3 Cust001 City1 1
4 Cust001 City2 1
5 Cust001 City2 3
6 Cust001 City3 1
7 Cust001 City3 1
8 Cust002 City1 2
9 Cust002 City1 1
10 Cust002 City2 3
11 Cust002 City1 2
12 Cust002 City2 1
13 Cust002 City3 2
14 Cust002 City3 3
15 Cust003 City1 1
16 Cust003 City2 3
17 Cust003 City3 2
请注意,该表还具有created_at和updated_at字段。为了简单起见,我省略了这些字段。
最后我希望我的查询返回 Cust001:
ID Customer City Amount
3 Cust001 City1 1
5 Cust001 City2 3
7 Cust001 City3 1
对于 Cust002:
ID Customer City Amount
11 Cust002 City1 2
12 Cust002 City2 1
14 Cust002 City3 3
我试过了:
Table::where('Customer', 'Cust001')
->latest()
->groupBy('City')
->get()
and also
Table::select(DB::raw('t.*'))->from(DB::raw('(select * from table where Customer = \'Cust001\' order by created_at DESC) t'))
->groupBy('t.City')->get();
但它不断返回每组中最旧的记录(我想要最新的)。
我怎样才能实现这个目标?如果对你们来说更容易,您可以在这里编写 SQL 查询,我会找到一种方法将其“翻译”为 Laravel 语法。
根据以下信息获取每个城市每个客户的最新记录created_at
您可以使用自加入
DB::table('yourTable as t')
->select('t.*')
->leftJoin('yourTable as t1', function ($join) {
$join->on('t.Customer','=','t1.Customer')
->where('t.City', '=', 't1.City')
->whereRaw(DB::raw('t.created_at < t1.created_at'));
})
->whereNull('t1.id')
->get();
在普通 SQL 中,它会是这样的
select t.*
from yourTable t
left join yourTable t1
on t.Customer = t1.Customer
and t.City = t1.City
and t.created_at < t1.created_at
where t1.id is null
Demo http://rextester.com/FGRUTC88212
另一种自内联的方法是
select t.*
from yourTable t
join (
select Customer,City,max(ID) ID
from yourTable
group by Customer,City
) t1
on t.Customer = t1.Customer
and t.City = t1.City
and t.ID = t1.ID
Demo http://rextester.com/CZTY41736
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)