repos_main/数据库/SQL示例脚本/铂金版订单服务状态和订单取件状态示例.sql

use [lowdb2]
--订单的取件状态;
--创建非聚合索引,加快查询速度;
DROP INDEX [IndexOrderPickup] ON [dbo].[tb_ErpOrderProductList]
GO

SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IndexOrderPickup] ON [dbo].[tb_ErpOrderProductList]
(
	[OPlist_Type] ASC
)
INCLUDE ( 	
	[OPlist_OrderNumber],
	[OPlist_ViceNumber],
	[OPlist_PickupStatus]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

;
with
------------------------------------------------------------------------------------------------------
--订单的服务状态;
cte_OrderServices as (
select [Ws_Number] as OrderNumber
,[Ws_ViceNumber] as ViceNumber
,(case 
when count(1) = count(case when [Ws_Status] = '0' then '未开始' end)	then 0		   -- 完全未开始;
when count(1) != count(case when [Ws_Status] = '1' then '完成' end)		then 1		-- 部分进行中;
when count(1) = count(case when [Ws_Status] = '1' then '完成' end)		then 2		-- 全部完成;
end) as ServicesStatus
from [tb_ErpWeddingService]
group by [Ws_ViceNumber],[Ws_Number]
),
/*
--由于按ViceNumber分组, OrderNumber是相同的, 可以使用max或min来获取:
cte_OrderServices as (
select max([Ws_Number]) as OrderNumber
,[Ws_ViceNumber] as ViceNumber
,(case 
when count(1) = count(case when [Ws_Status] = '0' then '未开始' end)	then 0		   -- 完全未开始;
when count(1) != count(case when [Ws_Status] = '1' then '完成' end)		then 1		-- 部分进行中;
when count(1) = count(case when [Ws_Status] = '1' then '完成' end)		then 2		-- 全部完成;
end) as ServicesStatus
from [tb_ErpWeddingService]
group by [Ws_ViceNumber]
),
*/
------------------------------------------------------------------------------------------------------
--订单的取件状态;
cte_OrderPickup as(
select OPlist_OrderNumber as OrderNumber
,OPlist_ViceNumber as ViceNumber
--订单最后一次的取件日期
--,max(OPlist_PickupTime) as PickupTime
,(case 
when count(1) = count(case when OPlist_PickupStatus = 0 then '未取' end)	then 0		-- 完全未取;
when count(1) != count(case when OPlist_PickupStatus = 1 then '已取' end)	then 1		-- 取件中;
when count(1) = count(case when OPlist_PickupStatus = 1 then '已取' end)	then 2		-- 全部取完;
end) as PickupStatus
from tb_ErpOrderProductList
where OPlist_Type = '2' 
group by OPlist_ViceNumber,OPlist_OrderNumber
)select Ordv_Number, Ordv_ViceNumber, isnull(PickupStatus,0)PickupStatus, isnull(ServicesStatus,0)ServicesStatus from tb_ErpOrderDigital
Left Join cte_OrderServices ON cte_OrderServices.ViceNumber = Ordv_ViceNumber
Left Join cte_OrderPickup ON cte_OrderPickup.ViceNumber =  Ordv_ViceNumber
/*
--由于按ViceNumber分组, OrderNumber是相同的, 可以使用max或min来获取:
cte_OrderPickup as(
select max(OPlist_OrderNumber) as OrderNumber
,OPlist_ViceNumber as ViceNumber
--订单最后一次的取件日期
--,max(OPlist_PickupTime) as PickupTime
,(case 
when count(1) = count(case when OPlist_PickupStatus = 0 then '未取' end)	then 0		-- 完全未取;
when count(1) != count(case when OPlist_PickupStatus = 1 then '已取' end)	then 1		-- 取件中;
when count(1) = count(case when OPlist_PickupStatus = 1 then '已取' end)	then 2		-- 全部取完;
end) as PickupStatus
from tb_ErpOrderProductList
where OPlist_Type = '2' 
group by OPlist_ViceNumber
)select Ordv_Number, Ordv_ViceNumber, isnull(PickupStatus,0)PickupStatus, isnull(ServicesStatus,0)ServicesStatus from tb_ErpOrderDigital
Left Join cte_OrderServices ON cte_OrderServices.ViceNumber = Ordv_ViceNumber
Left Join cte_OrderPickup ON cte_OrderPickup.ViceNumber =  Ordv_ViceNumber
GO

如果包含取件时间,新建以下索引会加快查询:
CREATE NONCLUSTERED INDEX [IndexOrderPickup] ON [dbo].[tb_ErpOrderProductList]
(
	[OPlist_Type] ASC
)
INCLUDE ( 	
	[OPlist_OrderNumber],
	[OPlist_ViceNumber],
	[OPlist_PickupStatus],
	[OPlist_PickupTime],
	) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

--以下方法,在有些情况下更快
select max(OPlist_OrderNumber) OrderNumber
,OPlist_ViceNumber
,case sum(convert(int,OPlist_PickupStatus)) 	-- 如果OPlist_PickupStatus本身为整型,就不用再转换;
when 0 then 0			-- 未取;
when count(1) then 2  	-- 已取;
else 1 					--部分取件;
end as PickupStatus 
from tb_ErpOrderProductList where OPlist_Type = '2' group by OPlist_ViceNumber
*/
------------------------------------------------------------------------------------------------------
GO
-- 订单接单人(同一订单多个接单人串起来);
with cte_OrderMan as (
select OrdPe_OrderNumber, User_Name from tb_ErpOrdersPerson Left Join tb_ErpUser ON tb_ErpOrdersPerson.OrdPe_OrdersPerson = tb_ErpUser.User_EmployeeID
),
cte_OrderPerson as (
select 
tb1.OrdPe_OrderNumber as OrderNumber
,stuff((
select ','+ User_Name
from cte_OrderMan as tb0 where tb0.OrdPe_OrderNumber = tb1.OrdPe_OrderNumber
for xml path('')),1,1,'') as OrderPerson
from cte_OrderMan as tb1
group by tb1.OrdPe_OrderNumber
)select * from cte_OrderPerson
GO
/*
另一种写法 outer apply:
with cte_OrderMan as (
select OrdPe_OrderNumber, User_Name from tb_ErpOrdersPerson Left Join tb_ErpUser ON tb_ErpOrdersPerson.OrdPe_OrdersPerson = tb_ErpUser.User_EmployeeID
)
select * from (select distinct OrdPe_OrderNumber from cte_OrderMan) TbA 
	outer apply
	(select UserName = stuff(
	(select ',' + User_Name from cte_OrderMan TbB where TbB.OrdPe_OrderNumber = TbA.OrdPe_OrderNumber for xml path('')),1,1,'')
)M 
where UserName like '%果果%'
*/
------------------------------------------------------------------------------------------------------
GO
--拍照状态;
with cte_PhotographyStatus as(
select max(Ordpg_Number) Ordpg_Number
,Ordpg_ViceNumber
--获取最大的景点名称,但是当订单类型为婚纱订单时,由于婚纱订单的副订单号是一样的,会不准确;
,max(Ordpg_Sights) as Ordpg_Sights
--获取最大的景点拍照时间,但是当订单类型为婚纱订单时,由于婚纱订单的副订单号是一样的,会不准确;
,max(Ordpg_PhotographyTime) as Ordpg_PhotographyTime
 --获取最小的预约时间,但是当订单类型为婚纱订单时,由于婚纱订单的副订单号是一样的,会不准确
,min(Ordpg_ReservationPhotographyTime) as ReservationPhotographyTime
,case when count(case when Ordpg_PhotographyStatus = 1 then '拍照' end) = Count(1) then 1 else 0 end as Ordpg_PhotographyStatus
from tb_ErpOrdersPhotography 
group by Ordpg_ViceNumber
)select * from cte_PhotographyStatus
GO
/* 如果是纯粹的获取整个订单的拍照状态
GO
with cte_PhotographyStatus as(
select max(Ordpg_Number) Ordpg_Number
,Ordpg_ViceNumber
,case when count(case when Ordpg_PhotographyStatus = 1 then '拍照' end) = Count(1) then 1 else 0 end as Ordpg_PhotographyStatus
from tb_ErpOrdersPhotography 
group by Ordpg_ViceNumber
)select * from cte_PhotographyStatus
GO
*/
------------------------------------------------------------------------------------------------------
--订单客户表(同一订单的多客户串起来)
GO
with cte_OrderCustomer as (
	select
  	-- 订单号;
	[tb_ErpCustomerGroup].[GP_OrderNumber] 
  	-- 客户组ID;
	,[tb_ErpCustomerGroup].[GP_CustomerGroupID]
	-------------------------------------------------------------------------------------
	--客户人员表中的客户编号ID
	,[tb_ErpCustomerGroupMembers].[GM_CustomerID] 
	--是否为主联系人 1 为主联系人
	,[tb_ErpCustomerGroupMembers].[GM_Master] 
	-------------------------------------------------------------------------------------
	--客户姓名
	,[tb_ErpCustomer].[Cus_Name] 
	--客户姓名拼音
	,[tb_ErpCustomer].[Cus_NamePinyin] 
	--客户性别;0(false):男,1(true):女
	,(case  when [tb_ErpCustomer].[Cus_Sex] = 0 then '男' else '女' end) as [Cus_Sex]
	--客户QQ
	,[tb_ErpCustomer].[Cus_QQ] 
	--微信号;
	,[tb_ErpCustomer].[Cus_MicroSignal] 
	--电话;
	,[tb_ErpCustomer].[Cus_Telephone] 
	-------------------------------------------------------------------------------------
	from tb_ErpCustomerGroup Right Join tb_ErpCustomerGroupMembers ON tb_ErpCustomerGroup.GP_CustomerGroupID = tb_ErpCustomerGroupMembers.GM_CustomerGroupID
	Left JOIN tb_ErpCustomer ON tb_ErpCustomerGroupMembers.GM_CustomerID = tb_ErpCustomer.Cus_CustomerNumber
	),
	cte_OrderCustomers as(
		select tb0.GP_OrderNumber as OrderNumber
		,max(tb0.GP_CustomerGroupID)as GroupID
		,stuff((select ',' + tb1.Cus_Name from cte_OrderCustomer tb1 where tb0.GP_OrderNumber = tb1.GP_OrderNumber for xml path('')),1,1,'') as CustomersName
		,stuff((select ',' + tb1.Cus_Sex from cte_OrderCustomer tb1 where tb0.GP_OrderNumber = tb1.GP_OrderNumber for xml path('')),1,1,'') as CustomersGender
		,stuff((select ',' + tb1.Cus_Telephone from cte_OrderCustomer tb1 where tb0.GP_OrderNumber = tb1.GP_OrderNumber for xml path('')),1,1,'') as CustomersPhone
		from cte_OrderCustomer tb0 group by tb0.GP_OrderNumber
	)select * from cte_OrderCustomers
GO
/*
另一种写法 outer apply:
with cte_OrderCustomer as (
	select
  	-- 订单号;
	[tb_ErpCustomerGroup].[GP_OrderNumber] 
  	-- 客户组ID;
	,[tb_ErpCustomerGroup].[GP_CustomerGroupID]
	-------------------------------------------------------------------------------------
	--客户人员表中的客户编号ID
	,[tb_ErpCustomerGroupMembers].[GM_CustomerID] 
	--是否为主联系人 1 为主联系人
	,[tb_ErpCustomerGroupMembers].[GM_Master] 
	-------------------------------------------------------------------------------------
	--客户姓名
	,[tb_ErpCustomer].[Cus_Name] 
	--客户姓名拼音
	,[tb_ErpCustomer].[Cus_NamePinyin] 
	--客户性别;0(false):男,1(true):女
	,(case  when [tb_ErpCustomer].[Cus_Sex] = 0 then '男' else '女' end) as [Cus_Sex]
	--客户QQ
	,[tb_ErpCustomer].[Cus_QQ] 
	--微信号;
	,[tb_ErpCustomer].[Cus_MicroSignal] 
	--电话;
	,[tb_ErpCustomer].[Cus_Telephone] 
	-------------------------------------------------------------------------------------
	from tb_ErpCustomerGroup Right Join tb_ErpCustomerGroupMembers ON tb_ErpCustomerGroup.GP_CustomerGroupID = tb_ErpCustomerGroupMembers.GM_CustomerGroupID
	Left JOIN tb_ErpCustomer ON tb_ErpCustomerGroupMembers.GM_CustomerID = tb_ErpCustomer.Cus_CustomerNumber
	)
	select * from 	
	(select GP_OrderNumber as OrderNumber, max(GP_CustomerGroupID) GroupID from cte_OrderCustomer group by GP_OrderNumber) TA 
	outer apply
	(
		select 
		UserName = stuff((select ',' + Cus_Name from cte_OrderCustomer where TA.OrderNumber = GP_OrderNumber for xml path('')),1,1,'') 
		,UserGender = stuff((select ',' + Cus_Sex from cte_OrderCustomer where TA.OrderNumber = GP_OrderNumber for xml path('')),1,1,'')
		,UserTelephone = stuff((select ',' + Cus_Telephone from cte_OrderCustomer where TA.OrderNumber = GP_OrderNumber for xml path('')),1,1,'')
	)TB
*/
------------------------------------------------------------------------------------------------------
--拍照景点(主要是将婚纱的景点名串起来)
GO
with cte_OrderScenery as (
	select max(tb0.Ordpg_Number) as OrderNumber
	,tb0.Ordpg_ViceNumber as OrderViceNumber
	-- 景点名串;
	,stuff((select ',' + tb1.Ordpg_Sights from tb_ErpOrdersPhotography tb1 where tb0.Ordpg_ViceNumber = tb1.Ordpg_ViceNumber for xml path('')),1,1,'') as OrderScenerys
	-- 各景点摄影师串;
	,stuff((select ',' + tb1.Ordpg_Photographer from tb_ErpOrdersPhotography tb1 where tb0.Ordpg_ViceNumber = tb1.Ordpg_ViceNumber for xml path('')),1,1,'') as OrderPhotographers
	-- 各景点拍照状态串;
	,stuff((select ',' + tb1.Ordpg_PhotographyStatus from tb_ErpOrdersPhotography tb1 where tb0.Ordpg_ViceNumber = tb1.Ordpg_ViceNumber for xml path('')),1,1,'') as OrderPhotographyStatus
	from tb_ErpOrdersPhotography tb0 group by tb0.Ordpg_ViceNumber
)select * from cte_OrderScenery
GO
------------------------------------------------------------------------------------------------------