如何在laravel中使用union分组

xkftehaa 于 2023-04-07 发布在其他

关注(0)|答案(2)|浏览(216)

我有两个表，我想联合和联合后，他们我想分组的一列，我用在联合

以下是我尝试的：

$issuance = DB::table('issuance as i')
            ->select('i.issue_to as stud_id', 'i.created_on');

$stud= DB::table('transfer as t')
            ->select('t.transfer_to as stud_id', 't.created_on')
            ->union($issuance)
            ->select('stud_id', 'created_on', DB::raw('COUNT(*) as total_asset'))
            ->groupBy('stud_id')
            ->orderBy('created_on', 'DESC')->get();

这是我尝试的MySQL查询

"(select `stud_id`, `created_on`, COUNT(*) as total_asset from `transfer` as 
`t` group by `stud_id`) union (select `i`.`issued_to` as `stud_id`, `i`.`created_on` from 
`issuance` as `i`) order by `created_on` desc"

我真正想要的MySQL是这样的：

select stud_id, count(*) from ((select `t`.`transfered_to` as `stud_id`, 
`t`.`created_on` from `transfer` as `t`) union (select `i`.`issued_to` as 
`stud_id`, `i`.`created_on`, COUNT(*) as asset from `issuance` as `i`)) as t 
group by stud_id order by `created_on` desc

谢谢你的帮助

laravel

来源：https://stackoverflow.com/questions/57267312/how-to-group-by-with-union-in-laravel

2条答案

按热度按时间

toiithl61#

//试试这个例子，它会帮助你
$query1 = DB：：table（'table1 '）-〉where（....）;
$query2 = DB：：table（'table2 '）-〉where（....）;
$data = DB：：select（DB：：raw（'id，MAX（created_at）as max_created_at'）〉union（$query1）-〉union（$query2）-〉orderBy（'max_created_at'）-〉groupBy（'id '）-〉get（）;

赞(0）回复(0）举报 2023-04-07

kr98yfug2#

也许没人在乎，但经过一个小时的搜索，我明白了

return DB::query()
    ->select(['*'])
    ->from($query1->union($query2), 'tmp')
    ->groupBy(['common_field'])
    ->get();

for（Laravel 9）

赞(0）回复(0）举报 2023-04-07

我来回答

如何在laravel中使用union分组

2条答案

相关问题

热门标签

最新问答