有时候union或者union all比左连接查询速度快

原来的语句

 

select count(1) from  ( SELECT CustCode,ShopCode,CreateTime,UniqCode,SaleType,TotalMoney,ExamineStatus,[Status],DeleFlag,CreatorCode,CashMoney,CardGiftMoney,FreeMoney,BorrowMoney,CouponMoney,AlipayMoney,WeChatMoney,BankMoney,CardMoney,CustShopCode FROM t_ShopSerLog y where 1=1 and y.CreateTime >='2017/7/31 0:00:00' and y.CreateTime <'2017/8/1 0:00:00' ) a  left join t_Cust b on a.CustCode= b.UniqCode    left join t_CustVCard c on a.CustCode= c.CustUniqCode and a.ShopCode=c.ShopCode  where 1=1  and (a.Status=1 or a.ExamineStatus=-1 or a.SaleType=9) and a.DeleFlag=1 and a.ShopCode='a4fc6360-7d6d-41b5-907e-8815e2246e2b'  ;

 

 

修改后的语句，代码虽然看着多了，但是速度缺提升了很多

select count(1) from (
select
distinct
a.UniqCode,
a.CustCode,
a.SaleType,
a.TotalMoney,
b.Name as CustName,
c.CardNo,a.ExamineStatus,
IsUser=(select ISNULL(SUM(IsUse),0) from (select IsUse,SerLogCode,[Status] from t_ShopSerElement g where g.SerLogCode=a.UniqCode union all select IsUse,SerLogCode,[Status] from t_GoodsSale h where h.SerLogCode=a.UniqCode union all select 0,SerLogCode,[Status] from t_PackageSalesRecords i where i.SerLogCode=a.UniqCode)s),
DetailsName = stuff((SELECT ',' + Name FROM (select Name,SerLogCode,[Status] from t_ShopSerElement g where g.SerLogCode=a.UniqCode union all select Name,SerLogCode,[Status] from t_GoodsSale g where g.SerLogCode=a.UniqCode union all select CustSerPlanName,SerLogCode,[Status] from t_PackageSalesRecords g where g.SerLogCode=a.UniqCode) AS t FOR xml path('')), 1, 1, ''),
a.CreateTime
from (( SELECT Distinct CustCode,ShopCode,CreateTime,UniqCode,SaleType,TotalMoney,ExamineStatus,[Status],DeleFlag,CreatorCode,CashMoney,CardGiftMoney,FreeMoney,BorrowMoney,CouponMoney,AlipayMoney,WeChatMoney,BankMoney,CardMoney,CustShopCode FROM t_ShopSerLog y where 1=1 and datediff(DAY,'2017/7/31 0:00:00',y.CreateTime) >=0 and datediff(DAY,'2017/8/1 0:00:00',y.CreateTime) <0 ) a left join (SELECT DISTINCT ShopSerCode,UserCode from t_ShopSerWaiter) d on a.UniqCode=d.ShopSerCode ) left join t_Cust b on a.CustCode= b.UniqCode left join t_CustVCard c on a.CustCode= c.CustUniqCode and a.ShopCode=c.ShopCode where 1=1 and (a.Status=1 or a.ExamineStatus=-1 or a.SaleType=9) and a.DeleFlag=1 and a.ShopCode='a4fc6360-7d6d-41b5-907e-8815e2246e2b' and d.UserCode='e751987e-981e-49ee-a615-c45961ea580b'
UNION

select
distinct
a.UniqCode,
a.CustCode,
a.SaleType,
a.TotalMoney,
b.Name as CustName,
c.CardNo,a.ExamineStatus,
IsUser=(select ISNULL(SUM(IsUse),0) from (select IsUse,SerLogCode,[Status] from t_ShopSerElement g where g.SerLogCode=a.UniqCode union all select IsUse,SerLogCode,[Status] from t_GoodsSale h where h.SerLogCode=a.UniqCode union all select 0,SerLogCode,[Status] from t_PackageSalesRecords i where i.SerLogCode=a.UniqCode)s),
DetailsName = stuff((SELECT ',' + Name FROM (select Name,SerLogCode,[Status] from t_ShopSerElement g where g.SerLogCode=a.UniqCode union all select Name,SerLogCode,[Status] from t_GoodsSale g where g.SerLogCode=a.UniqCode union all select CustSerPlanName,SerLogCode,[Status] from t_PackageSalesRecords g where g.SerLogCode=a.UniqCode) AS t FOR xml path('')), 1, 1, ''),
a.CreateTime
from
( SELECT Distinct CustCode,ShopCode,CreateTime,UniqCode,SaleType,TotalMoney,ExamineStatus,[Status],DeleFlag,CreatorCode,CashMoney,CardGiftMoney,FreeMoney,BorrowMoney,CouponMoney,AlipayMoney,WeChatMoney,BankMoney,CardMoney,CustShopCode FROM t_ShopSerLog y where 1=1 and datediff(DAY,'2017/7/31 0:00:00',y.CreateTime) >=0 and datediff(DAY,'2017/8/1 0:00:00',y.CreateTime) <0 ) a left join t_Cust b on a.CustCode= b.UniqCode left join t_CustVCard c on a.CustCode= c.CustUniqCode and a.ShopCode=c.ShopCode where 1=1 and (a.Status=1 or a.ExamineStatus=-1 or a.SaleType=9) and a.DeleFlag=1 and a.ShopCode='a4fc6360-7d6d-41b5-907e-8815e2246e2b' and a.CreatorCode='e751987e-981e-49ee-a615-c45961ea580b'
)t