如何通过左JOIN，COUNT和GROUP在Laravel中编写此SQL查询？

发布于 2025-01-21 05:15:03 字数 957 浏览 0 评论 0原文

该数据库具有“订单”和“客户”表。我无法将它们连接到这个USGBU请求。

SELECT orders.serial_number, 
count(orders.order_id) as count , 
customers.customer_name FROM orders 
LEFT JOIN customers ON 
orders.customer_id = customers.customer_id 
GROUP By orders.serial_number;

我编写的代码如下：

$orders = Orders::select("orders.serial_number","customers.customer_name", DB::raw("COUNT(orders.order_id) as count"))
        ->leftJoin('customers','orders.customer_id','=','customers.customer_id')
        ->groupBy('orders.serial_number')
        ->get();

错误消息显示如下：

SQLSTATE[42000]: Syntax error or access violation: 1055 'alqorshop.customers.customer_name' isn't in GROUP BY (SQL: select `orders`.`serial_number`, `customers`.`customer_name`, COUNT(orders.order_id) as count from `orders` left join `customers` on `orders`.`customer_id` = `customers`.`customer_id` group by `orders`.`serial_number`)

原文

The database has "orders" and "customers" tables.
I can't connect them to this usgbu request.

SELECT orders.serial_number, 
count(orders.order_id) as count , 
customers.customer_name FROM orders 
LEFT JOIN customers ON 
orders.customer_id = customers.customer_id 
GROUP By orders.serial_number;

The code I wrote is as follows:

$orders = Orders::select("orders.serial_number","customers.customer_name", DB::raw("COUNT(orders.order_id) as count"))
        ->leftJoin('customers','orders.customer_id','=','customers.customer_id')
        ->groupBy('orders.serial_number')
        ->get();

An error message appears as follows:

SQLSTATE[42000]: Syntax error or access violation: 1055 'alqorshop.customers.customer_name' isn't in GROUP BY (SQL: select `orders`.`serial_number`, `customers`.`customer_name`, COUNT(orders.order_id) as count from `orders` left join `customers` on `orders`.`customer_id` = `customers`.`customer_id` group by `orders`.`serial_number`)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲世九天 2025-01-28 05:15:03

此SQL的正确查询是：

    SELECT orders.serial_number,
    customers.customer_name, 
    count(orders.order_id) as count
    FROM orders 
    LEFT JOIN customers ON 
    orders.customer_id = customers.customer_id 
    GROUP By orders.serial_number,customers.customer_name;

或者

    SELECT orders.serial_number,    
    count(orders.order_id) as count
    FROM orders     
    LEFT JOIN customers ON 
    orders.customer_id = customers.customer_id 
    GROUP By orders.serial_number;

您没有在您的组中按子句中包含“ customer.customer_name”，这就是为什么会出现错误的原因。
要么通过查询添加客户。

The correct query for this sql is:

    SELECT orders.serial_number,
    customers.customer_name, 
    count(orders.order_id) as count
    FROM orders 
    LEFT JOIN customers ON 
    orders.customer_id = customers.customer_id 
    GROUP By orders.serial_number,customers.customer_name;

    SELECT orders.serial_number,    
    count(orders.order_id) as count
    FROM orders     
    LEFT JOIN customers ON 
    orders.customer_id = customers.customer_id 
    GROUP By orders.serial_number;

You did not include "customers.customer_name" in your group by clause which is why it's giving an error.
Either add customers.customer_name to your group by query or remove customers.customer_name from select statement

回复收藏 0 原文