Multi-table query for SQL Server (left join)

Today wrote a SQL to get three times to calculate success, is previously written sq himself l never noticed, left join

DECLARE @SendIntervalDays int, @OrderIntervalDays int, @OrderDate DATETIMESET @SendIntervalDays = 7--SMS Send Interval daysSET @OrderIntervalDays = 30--The number of days the order has been signedSET @OrderDate = ' 2014-12-12 '--order timeSELECTO.userid, Max (o.orderid) OrderID, MAX (mobilephone) MobileFrom View_saleorder O with (NOLOCK)Left joins Spikeactivityorderrequest so with (NOLOCK) on O.userid=so. UserIDand O.mobilephone=so.mobile and DateDiff (D,createtime,getdate ()) >= @SendIntervalDays--Sent SMS time interval greater than 7 days this sentence is actually wrong, The latter condition is completely useless. This is a thinking problem, should first put the data source to, and then in the screening, not here to fix, there is done, put together, bigger picture not good ahWHERE o.province in (310000,320000,330000)and o.deliverysigndate >= @OrderDateand DateDiff (d,o.deliverysigndate,getdate ()) = @OrderIntervalDays--Group by O.useridUNION All--Seconds to kill ordersSELECTO.userid, Max (o.orderid) OrderID, MAX (mobilephone) MobileFrom View_saleorder O with (NOLOCK)INNER JOIN orderdiscountdetail OD (NOLOCK) on O.orderid=od. OrderIDWHERE OD. Saleactivityruletype = Tenand o.province in (310000,320000,330000)and o.deliverysigndate >= @OrderDate-- signedGroup by O.userid

--declare @SendIntervalDays int, @OrderIntervalDays int, @OrderDate datetime--set @SendIntervalDays =30     & nbsp;--sms Send interval days--set @OrderIntervalDays =7    --order has been signed for days--set @OrderDate = ' 2014-12-12 '  --order time; With TEMP as (--Seconds to kill order Select o.userid Userid,max (O.orderid) Orderid ,max (mobilephone) Mobile from View_ Saleorder O with (NOLOCK) INNER joins Orderdiscountdetail OD (NOLOCK) on O.orderid=od. Orderid where  datediff (d,o.deliverysigndate,getdate ()) = 0  --Day sign and   o.creationdate >= @ Orderdate and   OD. Saleactivityruletype = 10AND   o.province in (310000,320000,330000) Group by o.userid union   all--General Order Select o.userid Userid,max (O.orderid) Orderid ,max (mobilephone) Mobile from  View_ Saleorder o with (NOLOCK) WHERE DateDiff (d,o.deliverysigndate,getdate ()) = @OrderIntervalDays-The order has been signed for 7 days and O. CreationDate >= @OrderDate  and o.province in (310000,320000,330000) Group by O.userid)--Go to possibly repeat UseriDselect temp. UserID Userid,max (TEMP. OrderID) Orderid ,max (TEMP. Mobile) Mobile from TEMPLeft joins Spikeactivityorderrequest so with (NOLOCK) on (TEMP. Userid=so. UserID OR temp.mobile=so.mobile)WHERE (DateDiff (d,so. Createtime,getdate ()) >= @SendIntervalDays OR so. Createtime is NULL)--text messages that have been sent for more than 30 days should be all data sources assembled to filterGROUP by TEMP. Useridorder by TEMP. Userid

Think more about how to think more summarize and investigate more attention to performance, priority location data Source Filter data source

Multi-table query for SQL Server (left join)