一个知识点比较全的sql语句，（全连接，列合并，增加默认值）

本文链接：https://blog.youkuaiyun.com/Soap_Leutenant/article/details/83276663

本文展示了一段复杂的SQL查询代码，用于统计特定日期范围内药品注册及后续行动的数据，包括注册次数、首次扣留、二次扣留和最终扣留的次数。通过连接多个表并使用case when语句和聚合函数，此查询提供了药品管理系统的详细操作记录。

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

select 
*
from 
(
	select 
		CASE WHEN name1 is null then name2 ELSE name1 end  as name,
		COALESCE(REGCOUNT,0) as reg_count,
		COALESCE(DETAINED_FIRST,0) as DETAINED_FIRST,
		COALESCE(DETAINED_SEC,0) as DETAINED_SEC,
		COALESCE(DETAINED_FINISH,0) as DETAINED_FINISH
	from (
		SELECT 
		t1.name name1,t2.name name2,t2.DETAINED_FIRST,t2.DETAINED_SEC,t2.DETAINED_FINISH,t1.REGCOUNT 
		from(
			select 
				name,
				count(id) as REGCOUNT 
			from(
				SELECT reg.id,reg_time,needle_flag,reg_operator_id,u.name
				from eoims_drug_inf_registrations reg 
				LEFT JOIN 
					sys_users u on reg.reg_operator_id = u.id
				where
					reg.vflag=1
					and reg.needle_flag=1
					and reg.reg_time >='2018-10-14 00:00:00'
					and reg.reg_time <= '2018-10-14 23:59:59'
				) as t GROUP BY name
			) as t1 
		FULL JOIN (
			SELECT 
			T.name,
			sum(case when action =10 then 1 else 0 end) as DETAINED_FIRST,
			sum(case when action =11 then 1 else 0 end) as DETAINED_SEC,
			sum(case when action =12 then 1 else 0 end) as DETAINED_FINISH 
			from(
				select 
					u.name,a.action 
				from 
					eoims_drug_actions_record a 
				LEFT JOIN sys_users u on u.id=a.operator_id 
				where action in(10,11,12) 
					and a.operate_time >= '2018-10-14 00:00:00'
					AND a.operate_time <= '2018-10-14 23:59:59' 
					and a.vflag=1 
				)T GROUP BY T.name 	
			) as t2 on t1.name=t2.name 
		) as t3
	UNION all 

	SELECT 
		'合计' as name ,
		sum(reg_count) as reg_count,
		sum(DETAINED_FIRST) as DETAINED_FIRST,
		sum(DETAINED_SEC) as DETAINED_SEC,
		sum(DETAINED_FINISH) as DETAINED_FINISH 
	from( 
		select 
			CASE WHEN name1 is null then name2 ELSE name1 end  as name,
			COALESCE(REGCOUNT,0) as reg_count,
			COALESCE(DETAINED_FIRST,0) as DETAINED_FIRST,
			COALESCE(DETAINED_SEC,0) as DETAINED_SEC,
			COALESCE(DETAINED_FINISH,0) as DETAINED_FINISH
		from (
			SELECT 
				t1.name name1,t2.name name2,
t2.DETAINED_FIRST,t2.DETAINED_SEC,
t2.DETAINED_FINISH,t1.REGCOUNT 
			from(
				select 
					name,
					count(id) as REGCOUNT 
				from(
					SELECT reg.id,reg_time,needle_flag,reg_operator_id,u.name
					from eoims_drug_inf_registrations reg 
					LEFT JOIN 
							sys_users u on reg.reg_operator_id = u.id
					where
						reg.vflag=1
						and reg.needle_flag=1
						and reg.reg_time >='2018-10-14 00:00:00'
						and reg.reg_time <= '2018-10-14 23:59:59'
					) as t GROUP BY name
				) as t1 
			FULL JOIN (
					SELECT 
						T.name,
						sum(case when action =10 then 1 else 0 end) as DETAINED_FIRST,
						sum(case when action =11 then 1 else 0 end) as DETAINED_SEC,
						sum(case when action =12 then 1 else 0 end) as DETAINED_FINISH 
					from(
						select 
							u.name,a.action 
						from 
							eoims_drug_actions_record a 
						LEFT JOIN sys_users u on u.id=a.operator_id 
						where action in(10,11,12) 
							and a.operate_time >= '2018-10-14 00:00:00'
							AND a.operate_time <= '2018-10-14 23:59:59' 
							and a.vflag=1 
						)T GROUP BY T.name 	
					) as t2 on t1.name=t2.name 
			) as t3
		)W
)A