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

pandasen

于 2022-07-20 09:05:00 发布

阅读量1.2k

点赞数

分类专栏： mysql 文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/pandasen/article/details/125885005

版权

分销商销售业绩订单统计排行数据库查询

关键词由优快云通过智能技术生成

mysql 专栏收录该内容

3 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分销商表，分销订单表，主键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