无聊写了个日报汇总sql_sql 日报-优快云博客

本文链接：https://blog.youkuaiyun.com/pand0118/article/details/124839052

此博客展示了复杂的SQL查询，用于统计特定日期后的订单数据，包括累计下单经销商数、下单总数、审单完成数、转单成功数、需求箱数、需求百升数以及订单处理时长等指标，通过多个LEFT JOIN实现多表联接操作。

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

废话不多说，就是各种拼接 left join

set @mydate ='2022-05-19';
set @orderdate ='2022-04-18';

select a55.*,a7.4月18日开始下单经销商数 from (
select a44.*,a6.下单时长,a6.1节点平均审核时长,a6.2节点平均审核时长,a6.发货时长 from (
	select a33.*,a5.需求箱数,a5.需求百升数 from (
		select a22.*,a4.转单成功数 from (
			select a11.*,a3.审单完成数 from (
				select a1.*,a2.当时段下单经销商数,a2.当时段下单总数
				from (select company_code,count(DISTINCT code) as '累计下单经销商数'
				-- ,count(distinct orderCode) as '累计下单总数'
				from carlsberg_auth.cib_order1
				where status not in ('关闭','已取消','已删除','确认失败')
				group by company_code) as a1 left join (
					select company_code,count(DISTINCT code) as '当时段下单经销商数'
					,count(distinct orderCode) as '当时段下单总数'
					from carlsberg_auth.cib_order1
					where status not in ('关闭','已取消','已删除','确认失败')
					and mydatetime>=@mydate
					group by company_code)as a2 
				on a1.company_code=a2.company_code) as a11 left join (select company_code
			,count(distinct orderCode) as '审单完成数'
			from carlsberg_auth.cib_order1
			where status not in ('关闭','已取消','已删除','确认失败','待确认','确认中')
			and mydatetime>=@mydate
			group by company_code) as a3
			on a11.company_code=a3.company_code
		) as a22 left join (select company_code
		,count(distinct orderCode) as '转单成功数'
		from carlsberg_auth.cib_order1
		where status not in ('关闭','已取消','已删除','确认失败') and result='成功'
		and mydatetime>=@mydate
		group by company_code) as a4 on a22.company_code=a4.company_code
		)as a33 left join (select company_code
		,sum(needCount) as '需求箱数'
		,ROUND(sum(hl),2) as '需求百升数'
		,sum(scanCount) as '订单发货箱数'
		from carlsberg_auth.cib_order
		where status not in ('关闭','已取消','已删除','确认失败') and result='成功' and sku_code not like '4%'
		and mydatetime>=@mydate
		group by company_code) as a5
		on a33.company_code=a5.company_code
	) a44 left join (select company_code
	,count(distinct orderCode) as '当日转单成功数'
	,ROUND(sum(convert(time1, UNSIGNED INTEGER))/count(*),2) as '下单时长'
	,ROUND(sum(convert(time2, UNSIGNED INTEGER))/count(*),2) as '1节点平均审核时长'
	,ROUND(sum(convert(time3, UNSIGNED INTEGER))/count(*),2) as '2节点平均审核时长'
	,ROUND(sum(convert(timelong, UNSIGNED INTEGER))/count(*),2) as '发货时长'
	from carlsberg_auth.cib_order1
	where status not in ('关闭','已取消','已删除','确认失败') and result='成功'
	and mydatetime>=@mydate
	group by company_code) as a6
	on a44.company_code=a6.company_code
) as a55 left join (SELECT company_code,count(DISTINCT code) as '4月18日开始下单经销商数'
from carlsberg_auth.cib_order1
where DATE_FORMAT(mydatetime,'%Y-%m-%d')>=@orderdate and status not in ('关闭','已取消','已删除','确认失败')
group by company_code) as a7
on a55.company_code=a7.company_code;