select convert(varchar(10),ca.call_time,20) as countDate,callin_dest_number,count(*) as countCallIn,
(select count(*) from as_call where seat_type=1
and call_type in(1,3,5)
and call_seat!=-1
and callin_dest_number=ca.callin_dest_number and user_number not in('02154265719','02154265471')
and convert(varchar(10),call_time,20)=convert(varchar(10),ca.call_time,20)) as countCallOn, (select count(*) from as_call as c where seat_type=1
and call_type in(1,5)
and record_starttime is null and record_endtime is not null
and callin_dest_number=ca.callin_dest_number and user_number not in('02154265719','02154265471')
and convert(varchar(10),call_time,20)=convert(varchar(10),ca.call_time,20)
and datediff(ss,record_endtime,
(select min(add_time) from as_phone_state where phone_state=10
and state_id in(select min(state_id) from bst_phone_state
where worker_id=c.tsr_id and worker_type=c.seat_type
and seat_number=c.call_seat and phone_state!=11
and add_time>=c.record_endtime)
))<=3) as count_zhaijifrom as_call as ca
where call_type in(1,3,5)
and callin_dest_number not in('2134014648') and user_number not in('02154265719','02154265471')
and ca.call_time>='2008-08-01' and ca.call_time<='2008-08-01 23:59:59'
group by convert(varchar(10),ca.call_time,20),callin_dest_number order by countDate
(select count(*) from as_call where seat_type=1
and call_type in(1,3,5)
and call_seat!=-1
and callin_dest_number=ca.callin_dest_number and user_number not in('02154265719','02154265471')
and convert(varchar(10),call_time,20)=convert(varchar(10),ca.call_time,20)) as countCallOn, (select count(*) from as_call as c where seat_type=1
and call_type in(1,5)
and record_starttime is null and record_endtime is not null
and callin_dest_number=ca.callin_dest_number and user_number not in('02154265719','02154265471')
and convert(varchar(10),call_time,20)=convert(varchar(10),ca.call_time,20)
and datediff(ss,record_endtime,
(select min(add_time) from as_phone_state where phone_state=10
and state_id in(select min(state_id) from bst_phone_state
where worker_id=c.tsr_id and worker_type=c.seat_type
and seat_number=c.call_seat and phone_state!=11
and add_time>=c.record_endtime)
))<=3) as count_zhaijifrom as_call as ca
where call_type in(1,3,5)
and callin_dest_number not in('2134014648') and user_number not in('02154265719','02154265471')
and ca.call_time>='2008-08-01' and ca.call_time<='2008-08-01 23:59:59'
group by convert(varchar(10),ca.call_time,20),callin_dest_number order by countDate
解决方案 »
- 查询时,能不能给一个起始编号(ID),然后自动加1生以下
- 能不能说一下 go和;在sql中的区别啊
- 都开3贴了(存储过程问题)
- SQL2005 通过xml字符串批量插入主从表
- 我想把EXEC('SELECT ID--- ')的结果赋给WHERE ID IN (---),但是这样不行,WHERE ID IN (EXEC('SELECT ID--- '),提示错误
- 请问c语言能否使用sqlserver2000(急,在线等待)
- 怎么才能把数据从一个服务器搬到另一个服务器
- 我想学习数据库,但我是个莱鸟,不知道应该怎样学,麻烦大家帮帮忙告诉我好不好,应该先学什么后学什么,告诉我一个大概的框架好不?谢谢
- exec 语句执行了一个sql语句,产生了一个3条记录的表,我怎么把他的所有字段的字符连接起来并print出来或导入到一个文本里面
- 如何在存储过程中定义 text变量?
- 跪求一条sql语句
- SQL SEVER 2005安装 问题
改成存储过程用临时表吧。
仔细说一下,刚接手的项目,数据库有400W 条数据,查询要两分钟,感觉这个SQL写的有问题;由于是老项目,SQL都是在JSP里拼的,所以不能用存储过程,希望有人帮我看看这个问题,分数不多,10分求助;
sql2000以后
字查询试用cte...
是老项目,SQL都是在JSP里拼的,所以不能用存储过程
--把最后的where 条件换成这个试试where call_type in(1,3,5)
and callin_dest_number<>'2134014648' and user_number<>'02154265719' and user_number<>'02154265471'
and datediff(day,ca.call_time,'2008-08-01')=0
group by convert(varchar(10),ca.call_time,20),callin_dest_number order by countDate
select *
into temp_as_call as ca from as_call
where call_type in(1,3,5)
and callin_dest_number not in('2134014648') and user_number not in('02154265719','02154265471')
and ca.call_time>='2008-08-01' and ca.call_time <='2008-08-01 23:59:59'
select convert(varchar(10),ca.call_time,20) as countDate,callin_dest_number,count(*) as countCallIn,
(select count(*) from temp_as_call where seat_type=1
and call_type in(1,3,5)
and call_seat!=-1
and callin_dest_number=ca.callin_dest_number and user_number not in('02154265719','02154265471')
and convert(varchar(10),call_time,20)=convert(varchar(10),ca.call_time,20)) as countCallOn, (select count(*) from temp_as_call as c where seat_type=1
and call_type in(1,5)
and record_starttime is null and record_endtime is not null
and callin_dest_number=ca.callin_dest_number and user_number not in('02154265719','02154265471')
and convert(varchar(10),call_time,20)=convert(varchar(10),ca.call_time,20)
and datediff(ss,record_endtime,
(select min(add_time) from as_phone_state where phone_state=10
and state_id in(select min(state_id) from bst_phone_state
where worker_id=c.tsr_id and worker_type=c.seat_type
and seat_number=c.call_seat and phone_state!=11
and add_time>=c.record_endtime)
)) <=3) as count_zhaiji from temp_as_call as ca group by convert(varchar(10),ca.call_time,20),callin_dest_number order by countDate然后修改: