使用 Laravel 还原如下 SQL 语句?
有这样一条SQL语句
SELECT p.`name` as 'p_name', g.`name` as 'g_name',p.`status`, o.group_id, o.`money` as 'o_money',ohp.`money` as 'ohp_money', ohp.`nt_money` , DATE_FORMAT(ohp.`updated_at`, '%Y-%m-%d') as 'l_time' ,
DATE_FORMAT(ohp.`billed_at`, '%Y-%m-%d') as 'ls_time' , us.`name` as 'us_name' FROM ( select `order_id`, SUM(`money`) as `all_money`, SUM(`nt_money`) as `all_nt_money`, SUM(`sc_money`) as `all_sc_money`, MAX(DATE_FORMAT(`updated_at`, '%Y-%m-%d')) as `updated_at`, MAX(DATE_FORMAT(`billed_at`, '%Y-%m-%d')) as `billed_at`
from `order_has_payments` where `nt_money` is not null group by `order_id` ) as ohp
left join `orders` as o on ohp.`order_id` = o.`id`
left join `projects` as p on o.id = p.`order_id`
left join `groups` as g on o.group_id = g.id
left join `users` as us on p.`owner_id` = us.id
where o.deleted_at is null
order by o.group_id,ohp.billed_at DESC;
使用laravel的构造器来写怎么写,主要是临时表链接查询这一块没找到教程啊,求大佬帮助?
其实可以简化一下,核心问题是如何使用临时表做连查询,所以可以简化成这样:
SELECT o.`money` , ohp.`nt_money` FROM ( select `order_id`, SUM(`nt_money`) as `nt_money`
from `order_has_payments` where `nt_money` is not null group by `order_id` ) as ohp
left join `orders` as o on ohp.`order_id` = o.`id`;
推荐文章: