sql server 的多表查询(left join )

 今天写了一个sql 弄了三遍才算成功,是以前写sq自己l从来没有注意过得,left join 

 

DECLARE @SendIntervalDays INT ,@OrderIntervalDays INT ,@OrderDate DATETIME

SET @SendIntervalDays=7      --短信发送间隔天数

SET @OrderIntervalDays=30    --订单已签收天数

SET @OrderDate='2014-12-12'  --订单时间

SELECT 

O.UserID

,Max(O.OrderID) OrderID 

,MAX(MobilePhone) Mobile 

FROM View_SaleOrder O WITH(NOLOCK)

LEFT JOIN SpikeActivityOrderRequest SO WITH(NOLOCK) ON O.UserID=SO.UserID 

AND O.MobilePhone=SO.mobile AND DateDiff(d,CreateTime,getdate()) >= @SendIntervalDays --已发送短信时间隔大于7天  这句其实是不对的,后面那个条件完全是没有用的 这是一个思维问题,应该先把数据源去到,然后在去筛选,不是这边弄好了,那里弄好了,拼到一起的,大局观不咋好啊

WHERE O.Province in (310000,320000,330000)  

AND O.DeliverySignDate >= @OrderDate 

AND DateDiff(d,O.DeliverySignDate,getdate()) = @OrderIntervalDays --

Group by O.UserID 

UNION  ALL

--秒杀订单

SELECT 

O.UserID

,Max(O.OrderID) OrderID 

,MAX(MobilePhone) Mobile 

FROM View_SaleOrder O WITH(NOLOCK)

INNER JOIN OrderDiscountDetail OD (NOLOCK) ON O.OrderID=OD.OrderID 

WHERE OD.SaleActivityRuleType = 10 

AND  O.Province in (310000,320000,330000) 

AND  O.DeliverySignDate >= @OrderDate --已签收

Group by O.UserID 

 

 

--DECLARE @SendIntervalDays INT ,@OrderIntervalDays INT ,@OrderDate DATETIME

--SET @SendIntervalDays=30      --短信发送间隔天数

--SET @OrderIntervalDays=7    --订单已签收天数

--SET @OrderDate='2014-12-12'  --订单时间

;WITH TEMP AS(

--秒杀订单

SELECT 

O.UserID UserID

,Max(O.OrderID) OrderID 

,MAX(MobilePhone) Mobile 

FROM View_SaleOrder O WITH(NOLOCK)

INNER JOIN OrderDiscountDetail OD (NOLOCK) ON O.OrderID=OD.OrderID 

WHERE  DateDiff(d,O.DeliverySignDate,getdate()) = 0  --当天签收

AND   O.CreationDate >= @OrderDate 

AND   OD.SaleActivityRuleType = 10

AND   O.Province in (310000,320000,330000)

Group by O.UserID 

UNION  ALL

--普通订单

SELECT 

O.UserID UserID

,Max(O.OrderID) OrderID 

,MAX(MobilePhone) Mobile 

FROM  View_SaleOrder O WITH(NOLOCK)

WHERE DateDiff(d,O.DeliverySignDate,getdate()) = @OrderIntervalDays --订单已签收天数7天

AND O.CreationDate >= @OrderDate 

AND O.Province in (310000,320000,330000)

Group by O.UserID )

--去可能重复userID

SELECT 

TEMP.UserID UserID

,Max(TEMP.OrderID) OrderID 

,MAX(TEMP.Mobile) Mobile 

FROM TEMP

LEFT JOIN 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) --已发送短信时间隔大于30天  应当是所有的数据源拼装好了 在去筛选

GROUP BY TEMP.UserID

ORDER BY TEMP.UserID

 

多想想 多思考 多总结 多追究  深入研究  多注意性能,优先定位数据源 筛选数据源的