sql语句如下:select u.username,
convert(varchar(7),i.create_date,121) date,
count(*) all_count
from t_imageorder i,t_user u
where convert(varchar(7),i.create_date,121) between '2010-10' and '2010-10'
and i.DEALER=u.id
and u.username in
(
select distinct(u1.username) from t_user u1,t_user_role l
where u1.status<>1 and l.user_id=u1.id and l.role_id=1
)
group by all u.username,convert(varchar(7),i.create_date,121)
having convert(varchar(7),i.create_date,121) >= '2010-10' and convert(varchar(7),i.create_date,121)<='2010-10'
and u.username in
(
select distinct(u1.username) from t_user u1,t_user_role l
where u1.status<>1 and l.user_id=u1.id and l.role_id=1
)
当我查询日期一样时速度就很慢,如:
having convert(varchar(7),i.create_date,121) >= '2010-10' and convert(varchar(7),i.create_date,121)<='2010-10'
当不一样时就挺快的,请问是什么原因?怎么优化下比较好,请大侠指导,最好贴出优化后的sql语句。谢谢
convert(varchar(7),i.create_date,121) date,
count(*) all_count
from t_imageorder i,t_user u
where convert(varchar(7),i.create_date,121) between '2010-10' and '2010-10'
and i.DEALER=u.id
and u.username in
(
select distinct(u1.username) from t_user u1,t_user_role l
where u1.status<>1 and l.user_id=u1.id and l.role_id=1
)
group by all u.username,convert(varchar(7),i.create_date,121)
having convert(varchar(7),i.create_date,121) >= '2010-10' and convert(varchar(7),i.create_date,121)<='2010-10'
and u.username in
(
select distinct(u1.username) from t_user u1,t_user_role l
where u1.status<>1 and l.user_id=u1.id and l.role_id=1
)
当我查询日期一样时速度就很慢,如:
having convert(varchar(7),i.create_date,121) >= '2010-10' and convert(varchar(7),i.create_date,121)<='2010-10'
当不一样时就挺快的,请问是什么原因?怎么优化下比较好,请大侠指导,最好贴出优化后的sql语句。谢谢
<='2010-10'你这个逻辑不是取全部的时间吗?
还有就是那个时间是不是可以换个方式
如where i.create_date between '2010-10-01' and '2010-10-31'
(
select distinct(u1.username) from t_user u1,t_user_role l
where u1.status<>1 and l.user_id=u1.id and l.role_id=1
) 这条怎么出现了两次?
username.in用了两次是因为有两次查询嘛,都需要用到这个条件。