mysql 统计分销商订单销售额及销售量排行

最新推荐文章于 2023-05-22 15:22:59 发布

原创最新推荐文章于 2023-05-22 15:22:59 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

mysql 专栏收录该内容

3 篇文章

订阅专栏

该查询用于统计分销商的销售业绩，并进行排行。通过LEFT JOIN分销订单表，分别获取first_user_id, second_user_id, third_user_id对应的订单金额和数量，然后计算总金额和总订单数。查询条件限定在wxapp_id为10001且未被删除的分销商。结果按总金额降序和创建时间降序排列。

分销商表，分销订单表，主键user_id

分销订单表,涵盖first_user_id,second_user_id,third_user_id

需要统计分销商的销售业绩并做排行

SELECT
	dealer.*, dealer.money + dealer.freeze_money + dealer.total_money AS total_amount,
	IFNULL(
		sum(firstOrder.order_price),
		0
	) + IFNULL(
		sum(secondOrder.order_price),
		0
	) + IFNULL(
		sum(thirdOrder.order_price),
		0
	) AS total_order_amount,
	count(firstOrder.order_id) + count(secondOrder.order_id) + count(thirdOrder.order_id) AS total_order_nums
FROM
	`5f_dealer_user` `dealer`
LEFT JOIN `5f_dealer_order` `firstOrder` ON `firstOrder`.`first_user_id` = `dealer`.`user_id`
LEFT JOIN `5f_dealer_order` `secondOrder` ON `secondOrder`.`second_user_id` = `dealer`.`user_id`
LEFT JOIN `5f_dealer_order` `thirdOrder` ON `thirdOrder`.`third_user_id` = `dealer`.`user_id`
WHERE
	(
		`dealer`.`wxapp_id` = 10001
		AND `dealer`.`wxapp_id` = 10001
	)
AND `dealer`.`is_delete` = 0
GROUP BY
	`dealer`.`user_id`
ORDER BY
	`total_amount` DESC,
	`dealer`.`create_time` DESC