SELECT top 20 *,CONVERT(varchar(20) , HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , InboundDateTime, 20) AS InboundDateTime from InboundLog a
WHERE NOT EXISTS(
SELECT * FROM InboundLog b WHERE a.id<b.id AND b.InboundDateTime >= '2010-01-01 00:00:00' and b.InboundDateTime <= '2010-01-31 23:59:59'
AND a.callerID=b.callerID AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID AND
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1 )
AND a.InboundDateTime >= '2010-01-01 00:00:00' and a.InboundDateTime <= '2010-01-31 23:59:59'
order by InboundDateTime desc 为什么加了a.id<b.id 这个查询条件,速度就慢很多,不加的话,就快。。
CONVERT(varchar(20) , InboundDateTime, 20) AS InboundDateTime from InboundLog a
WHERE NOT EXISTS(
SELECT * FROM InboundLog b WHERE a.id<b.id AND b.InboundDateTime >= '2010-01-01 00:00:00' and b.InboundDateTime <= '2010-01-31 23:59:59'
AND a.callerID=b.callerID AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID AND
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1 )
AND a.InboundDateTime >= '2010-01-01 00:00:00' and a.InboundDateTime <= '2010-01-31 23:59:59'
order by InboundDateTime desc 为什么加了a.id<b.id 这个查询条件,速度就慢很多,不加的话,就快。。
top 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime from InboundLog a,(SELECT
callerID,
local_dial_number,
campaignScriptID,
MAX(ID)BID
FROM
InboundLog b
WHERE DATEDIFF(MM, b.InboundDateTime ,'2010-01-01')=0GROUP BY callerID, local_dial_number,campaignScriptID)AS BWHERE
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1
AND
DATEDIFF(MM, A.InboundDateTime ,'2010-01-01')=0
AND a.callerID=b.callerID
AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID
order by A.InboundDateTime desc
top 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime from InboundLog a,(SELECT
callerID,
local_dial_number,
campaignScriptID,
MAX(ID)BID
FROM
InboundLog b
WHERE DATEDIFF(MM, b.InboundDateTime ,'2010-01-01')=0GROUP BY callerID, local_dial_number,campaignScriptID)AS BWHERE
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1
AND A.ID=B.BID AND
DATEDIFF(MM, A.InboundDateTime ,'2010-01-01')=0
AND a.callerID=b.callerID
AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID
order by A.InboundDateTime desc 相应连接条件上加索引,
InboundDateTime 这个字段考虑加聚集索引,
你的,我试了下,运行时间比之前的还要慢。
帮我看下这个问题吧:
select top 1 dbo.InboundLog.id from InboundLog as a 这种情况要怎么指定 dbo.InboundLog.id
SELECT TOP 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime
FROM InboundLog A
WHERE DATEADD(HH,a.inboundDateTime,MAX(a.inboundDateTime)) <= 0
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEDIFF(hh,max(inboundDateTime),a.inboundDateTime) <= 0
SELECT TOP 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime
FROM InboundLog A
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEDIFF(hh,max(inboundDateTime),a.inboundDateTime) <= 0
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime
FROM InboundLog A
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEADD(HH,1,A.inboundDateTime) <= max(inboundDateTime)
ORDER BY A.InboundDateTime DESC
CONVERT(varchar(20) , InboundDateTime, 20) AS InboundDateTime from InboundLog a
WHERE a.InboundDateTime between '2010-01-01' and '2010-01-31'
and NOT EXISTS(
SELECT * FROM InboundLog b WHERE b.InboundDateTime between '2010-01-01' and '2010-01-31'
AND a.callerID=b.callerID AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID
and a.id<b.id and DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1
)
order by InboundDateTime desc
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime,
COUNT(1)
FROM InboundLog A
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEADD(HH,1,A.inboundDateTime) <= max(inboundDateTime)
ORDER BY A.InboundDateTime DESC
忘记加聚集函数了