在一个存储过程里面的语句,通过这个查询,把表进行更新,实际运行中,除了索引已经建立外,已经不知道如何优化了,
--UPDATE dbo.CRM_Interunit SET Style='0000100015000010000100001',OldStyle=Style WHERE typeId IN
(
SELECT a.typeId FROM dbo.CRM_Interunit a Inner Join CRM_InterunitStyle b on a.Style=b.TypeId
INNER JOIN dbo.CRM_SalesTask c ON a.TypeID=c.InterunitTypeID
WHERE a.IsDeleted=0 AND b.IsBackToPublic=1 AND DATEADD(day, 2,c.CreateDate)<GETDATE()
AND a.TypeID NOT IN
(
--自己直接成交,不检查是否有跟单、进展记录
SELECT a.TypeID FROM dbo.CRM_Interunit a INNER JOIN dbo.CRM_SalesRegister b ON a.TypeID=b.BuyerTypeId
WHERE a.CreatorTypeID=b.CreatorTypeID
UNION all
--自己创建不超期的
SELECT a.typeId FROM dbo.CRM_Interunit a Inner Join CRM_InterunitStyle b on a.Style=b.TypeId
INNER JOIN dbo.CRM_SalesTask c ON a.TypeID=c.InterunitTypeID
WHERE a.IsDeleted=0 AND b.IsBackToPublic=1 AND DATEADD(day, 2,c.CreateDate)>GETDATE()
AND a.CreatorTypeID=c.CreatorTypeID
UNION all
--自己创建的不超期记录,包括单条不超期,和多条间隔不超期
SELECT typeId
FROM ( SELECT * ,
( SELECT MAX(createdate)
FROM
(
SELECT a.typeId,a.name,c.ID,c.CreateDate FROM CRM_Interunit a INNER JOIN CRM_SalesTask b ON a.TypeID=b.InterunitTypeID
LEFT JOIN dbo.CRM_SaleTaskRecord c On c.coSaleTaskTypeId=b.TypeID
WHERE a.CreatorTypeID=b.CreatorTypeID AND a.CreatorTypeID=c.CreatorTypeID
) AS b
WHERE b.typeid = a.typeid
AND b.createdate < a.createdate
) AS priordate
FROM
(
SELECT a.typeId,a.name,c.ID,c.CreateDate FROM CRM_Interunit a INNER JOIN CRM_SalesTask b ON a.TypeID=b.InterunitTypeID
LEFT JOIN dbo.CRM_SaleTaskRecord c On c.coSaleTaskTypeId=b.TypeID
WHERE a.CreatorTypeID=b.CreatorTypeID AND a.CreatorTypeID=c.CreatorTypeID
) AS a
WHERE NOT EXISTS ( SELECT 1
FROM (
SELECT a.typeId,a.name,c.ID,c.CreateDate FROM CRM_Interunit a INNER JOIN CRM_SalesTask b ON a.TypeID=b.InterunitTypeID
LEFT JOIN dbo.CRM_SaleTaskRecord c On c.coSaleTaskTypeId=b.TypeID
WHERE a.CreatorTypeID=b.CreatorTypeID AND a.CreatorTypeID=c.CreatorTypeID
) AS c
WHERE c.typeid = a.typeid
AND c.createdate > a.createdate )
) AS d
WHERE
(priordate IS NULL AND DATEADD(day, 2,CreateDate)>GETDATE()) OR
(priordate IS NOT NULL AND DATEADD(day,2,priordate)>d.CreateDate )
AND DATEADD(day, 2,CreateDate)>GETDATE()
)
)
--UPDATE dbo.CRM_Interunit SET Style='0000100015000010000100001',OldStyle=Style WHERE typeId IN
(
SELECT a.typeId FROM dbo.CRM_Interunit a Inner Join CRM_InterunitStyle b on a.Style=b.TypeId
INNER JOIN dbo.CRM_SalesTask c ON a.TypeID=c.InterunitTypeID
WHERE a.IsDeleted=0 AND b.IsBackToPublic=1 AND DATEADD(day, 2,c.CreateDate)<GETDATE()
AND a.TypeID NOT IN
(
--自己直接成交,不检查是否有跟单、进展记录
SELECT a.TypeID FROM dbo.CRM_Interunit a INNER JOIN dbo.CRM_SalesRegister b ON a.TypeID=b.BuyerTypeId
WHERE a.CreatorTypeID=b.CreatorTypeID
UNION all
--自己创建不超期的
SELECT a.typeId FROM dbo.CRM_Interunit a Inner Join CRM_InterunitStyle b on a.Style=b.TypeId
INNER JOIN dbo.CRM_SalesTask c ON a.TypeID=c.InterunitTypeID
WHERE a.IsDeleted=0 AND b.IsBackToPublic=1 AND DATEADD(day, 2,c.CreateDate)>GETDATE()
AND a.CreatorTypeID=c.CreatorTypeID
UNION all
--自己创建的不超期记录,包括单条不超期,和多条间隔不超期
SELECT typeId
FROM ( SELECT * ,
( SELECT MAX(createdate)
FROM
(
SELECT a.typeId,a.name,c.ID,c.CreateDate FROM CRM_Interunit a INNER JOIN CRM_SalesTask b ON a.TypeID=b.InterunitTypeID
LEFT JOIN dbo.CRM_SaleTaskRecord c On c.coSaleTaskTypeId=b.TypeID
WHERE a.CreatorTypeID=b.CreatorTypeID AND a.CreatorTypeID=c.CreatorTypeID
) AS b
WHERE b.typeid = a.typeid
AND b.createdate < a.createdate
) AS priordate
FROM
(
SELECT a.typeId,a.name,c.ID,c.CreateDate FROM CRM_Interunit a INNER JOIN CRM_SalesTask b ON a.TypeID=b.InterunitTypeID
LEFT JOIN dbo.CRM_SaleTaskRecord c On c.coSaleTaskTypeId=b.TypeID
WHERE a.CreatorTypeID=b.CreatorTypeID AND a.CreatorTypeID=c.CreatorTypeID
) AS a
WHERE NOT EXISTS ( SELECT 1
FROM (
SELECT a.typeId,a.name,c.ID,c.CreateDate FROM CRM_Interunit a INNER JOIN CRM_SalesTask b ON a.TypeID=b.InterunitTypeID
LEFT JOIN dbo.CRM_SaleTaskRecord c On c.coSaleTaskTypeId=b.TypeID
WHERE a.CreatorTypeID=b.CreatorTypeID AND a.CreatorTypeID=c.CreatorTypeID
) AS c
WHERE c.typeid = a.typeid
AND c.createdate > a.createdate )
) AS d
WHERE
(priordate IS NULL AND DATEADD(day, 2,CreateDate)>GETDATE()) OR
(priordate IS NOT NULL AND DATEADD(day,2,priordate)>d.CreateDate )
AND DATEADD(day, 2,CreateDate)>GETDATE()
)
)
修改为c.CreateDate<DATEADD(day, -2,GETDATE())再一个,非要写出这么长语句的吗?分成多个语句实现不行吗
1.所有使用GETDATE()的地方,可以在update之前,先定义一个DateTime变量,存入GETDATE()值,之后代码中用DateTime变量代替GETDATE()函数
2.NOT IN可以使用INNER JOIN代替,效率更高。
3.多个地方的select... from CRM_Interunit ...可以使用with方法代替
3楼的:
1。已修改
2.不明白,请明示
3.with的用法也不是很清楚目前没啥效果