多个连接产生不需要的结果

我的查询结果很奇怪。数字太离谱了，我不知道为什么。
以下是查询中使用的表的表结构：

CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `payment_method_id` int(11) DEFAULT NULL,
  `date` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `details` text COLLATE utf8mb4_unicode_ci,
  `ip` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Complete',
  `booked_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `booking_products` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `price_subtotal` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price_total` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `booking_services` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` int(11) NOT NULL,
  `service_id` int(11) NOT NULL,
  `reservations` int(11) NOT NULL,
  `price_subtotal` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price_total` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_methods_name_unique` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我的问题是：

return DB::table('bookings')
    ->selectRaw('payment_methods.name, count(bookings.id) as bookings, (sum(booking_services.price_subtotal) + sum(booking_products.price_subtotal)) as subtotal')
    ->join('booking_services', 'booking_services.booking_id', '=', 'bookings.id')
    ->join('booking_products', 'booking_products.booking_id', '=', 'bookings.id')
    ->join('payment_methods', 'payment_methods.id', '=', 'bookings.payment_method_id')
    ->where('bookings.status', 'Complete')
    ->whereBetween('bookings.booked_at', [$this->carbon_from, $this->carbon_to])
    ->groupBy('payment_methods.id')
    ->orderBy('payment_methods.name')
    ->get();
``` `$this->carbon_from` 以及 `$this->carbon_to` 是工作正常的碳素物体。
我正在试图获得总预订量和每个付款方式的价格小计的总和。它似乎是将预订产品/服务分组在一起，而不是按我想要的每个付款方式。
我是不是漏了什么？
编辑：以下是查询日志：

select payment_methods.name,
count(bookings.id) as bookings,
(sum(booking_services.price_subtotal) + sum(booking_products.price_subtotal)) as subtotal
from bookings
inner join booking_services on booking_services.booking_id = bookings.id
inner join booking_products on booking_products.booking_id = bookings.id
inner join payment_methods on payment_methods.id = bookings.payment_method_id
where bookings.status = ? and bookings.booked_at between ? and ?
group by payment_methods.id
order by payment_methods.name asc

我猜你得到的是叉积，这就是为什么你得到错误的聚合数，我建议你，计算每个子子句中的和，然后将这些子句与你的主查询连接起来，比如

SELECT p.name,
       COUNT(DISTINCT b.id) AS bookings,
       bs.price_subtotal + bp.price_subtotal AS subtotal
FROM bookings b
INNER JOIN ( 
    SELECT booking_id, SUM(price_subtotal) price_subtotal
    FROM booking_services
    GROUP BY booking_id
) bs ON b.id = bs.booking_id
INNER JOIN (
    SELECT booking_id, SUM(price_subtotal) price_subtotal
    FROM booking_products
    GROUP BY booking_id
) bp ON b.id = bp.booking_id
INNER JOIN payment_methods p ON p.id = b.payment_method_id
WHERE b.status = ? 
  AND b.booked_at BETWEEN ? AND ? 
GROUP BY p.name
ORDER BY p.name

我不知道如何使用laravel的查询生成器/雄辩的方式转换/编写上述查询

多个连接产生不需要的结果

2条答案

相关问题

热门标签

最新问答