今天写了一个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
多想想 多思考 多总结 多追究 深入研究 多注意性能,优先定位数据源 筛选数据源的