//排队挂机数
select RingDuration,QueueDuration from EXTERNAL_CDR.dbo.CDRMAIN where sessionid not in (select sessionid from EXTERNAL_CDR.dbo.CDRMAIN where TalkDuration>0 and sessionid in(select sessionid from EXTERNAL_CDR.dbo.CDRMAIN where TalkDuration=0 )) and sessionid in(select sessionid from EXTERNAL_CDR.dbo.CDRMAIN tic where TalkDuration=0 and Direction=1 and exists(select 1 from dbo.t_user_Users tuu where exists (select 1 from dbo.t_user_UserCombo uc left join dbo.t_user_Users uu on uc.c_userID=uu.c_userID where uu.c_state=1 and uc.c_comboID='559' and uc.c_beginTime <=getDate() and uc.c_endTime>getDate() and uc.c_state=1 and tuu.c_userID=uc.c_userID) and tic.CallerNum_bak=tuu.c_mobile) and starttime>=1330531200 and starttime<1331049600)
select RingDuration,QueueDuration from EXTERNAL_CDR.dbo.CDRMAIN where sessionid not in (select sessionid from EXTERNAL_CDR.dbo.CDRMAIN where TalkDuration>0 and sessionid in(select sessionid from EXTERNAL_CDR.dbo.CDRMAIN where TalkDuration=0 )) and sessionid in(select sessionid from EXTERNAL_CDR.dbo.CDRMAIN tic where TalkDuration=0 and Direction=1 and exists(select 1 from dbo.t_user_Users tuu where exists (select 1 from dbo.t_user_UserCombo uc left join dbo.t_user_Users uu on uc.c_userID=uu.c_userID where uu.c_state=1 and uc.c_comboID='559' and uc.c_beginTime <=getDate() and uc.c_endTime>getDate() and uc.c_state=1 and tuu.c_userID=uc.c_userID) and tic.CallerNum_bak=tuu.c_mobile) and starttime>=1330531200 and starttime<1331049600)
主意确认决胜属性字段,where,group by,order by后面出现的字段加索引
--in、 not in、 exists 用的太多了
SELECT RingDuration ,
QueueDuration
FROM EXTERNAL_CDR.dbo.CDRMAIN
WHERE sessionid NOT IN (
SELECT sessionid
FROM EXTERNAL_CDR.dbo.CDRMAIN
WHERE TalkDuration > 0
AND sessionid IN ( SELECT sessionid
FROM EXTERNAL_CDR.dbo.CDRMAIN
WHERE TalkDuration = 0 ) )
AND sessionid IN (
SELECT sessionid
FROM EXTERNAL_CDR.dbo.CDRMAIN tic
WHERE TalkDuration = 0
AND Direction = 1
AND EXISTS ( SELECT 1
FROM dbo.t_user_Users tuu
WHERE EXISTS ( SELECT 1
FROM dbo.t_user_UserCombo uc
LEFT JOIN dbo.t_user_Users uu ON uc.c_userID = uu.c_userID
WHERE uu.c_state = 1
AND uc.c_comboID = '559'
AND uc.c_beginTime <= GETDATE()
AND uc.c_endTime > GETDATE()
AND uc.c_state = 1
AND tuu.c_userID = uc.c_userID )
AND tic.CallerNum_bak = tuu.c_mobile )
AND starttime >= 1330531200
AND starttime < 1331049600 )
你的SQL语句太庞大了,子查询太多了
SELECT sessionid
FROM EXTERNAL_CDR.dbo.CDRMAIN
WHERE TalkDuration > 0
AND sessionid IN ( SELECT sessionid
FROM EXTERNAL_CDR.dbo.CDRMAIN
WHERE TalkDuration = 0 )
--这句的意思就是得到TalkDuration既大于0又有等于0的sessionid
--等价于SELECT sessionid FROM
( SELECT * FROM EXTERNAL_CDR.dbo.CDRMAIN WHERE TalkDuration > 0
UNION ALL
SELECT * FROM EXTERNAL_CDR.dbo.CDRMAIN WHERE TalkDuration = 0
) a GROUP BY sessionid HAVING ( COUNT(1) > 1 )
;WITH m AS
(
SELECT sessionid FROM
( SELECT * FROM EXTERNAL_CDR.dbo.CDRMAIN WHERE TalkDuration > 0
UNION ALL
SELECT * FROM EXTERNAL_CDR.dbo.CDRMAIN WHERE TalkDuration = 0
) a GROUP BY sessionid HAVING ( COUNT(1) > 1 )
),m1 AS
(
SELECT sessionid
FROM EXTERNAL_CDR.dbo.CDRMAIN tic
WHERE TalkDuration = 0
AND Direction = 1
AND EXISTS ( SELECT 1
FROM dbo.t_user_Users tuu
WHERE EXISTS ( SELECT 1
FROM dbo.t_user_UserCombo uc
LEFT JOIN dbo.t_user_Users uu ON uc.c_userID = uu.c_userID
WHERE uu.c_state = 1
AND uc.c_comboID = '559'
AND uc.c_beginTime <= GETDATE()
AND uc.c_endTime > GETDATE()
AND uc.c_state = 1
AND tuu.c_userID = uc.c_userID )
AND tic.CallerNum_bak = tuu.c_mobile )
AND starttime >= 1330531200
AND starttime < 1331049600
)SELECT RingDuration ,
QueueDuration
FROM EXTERNAL_CDR.dbo.CDRMAIN a
LEFT JOIN m b ON a.sessionid = b.sessionid
LEFT JOIN m1 c ON a.sessionid = c.sessionid
WHERE b.sessionid IS NULL
AND c.sessionid IS NOT NULL