select * from(
select top 20 * from (
select moo.linkid,moo.mobile,moo.matchcode,mocontent,motime,moo.spcode,moo.stat,moo.state,moo.company,mt.linkid mtlinkid,mtcontent,mttime,mt.stat mtstat,RowNumber
from(
select linkid,mobile,mo.matchcode,mocontent,motime,spcode,stat,Province state,company,ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
from t_jbxs_mo_log mo,t_mobile_zipcode mobi,t_jbxs_matchinfo matc
where substring(mo.mobile,0,8)=substring(mobi.Region,0,8) and matc.matchcode=mo.matchcode ) moo left join t_jbxs_mt_log mt on mt.linkid=moo.linkid
) result where RowNumber>(100-1)*20 ) resultOrder order by motime desc这是我的sql语句,在查询分析器中一般都是2秒左右,在系统中有时候很快,有时候却很慢超时查不出数据来。不知道是怎么回事
select top 20 * from (
select moo.linkid,moo.mobile,moo.matchcode,mocontent,motime,moo.spcode,moo.stat,moo.state,moo.company,mt.linkid mtlinkid,mtcontent,mttime,mt.stat mtstat,RowNumber
from(
select linkid,mobile,mo.matchcode,mocontent,motime,spcode,stat,Province state,company,ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
from t_jbxs_mo_log mo,t_mobile_zipcode mobi,t_jbxs_matchinfo matc
where substring(mo.mobile,0,8)=substring(mobi.Region,0,8) and matc.matchcode=mo.matchcode ) moo left join t_jbxs_mt_log mt on mt.linkid=moo.linkid
) result where RowNumber>(100-1)*20 ) resultOrder order by motime desc这是我的sql语句,在查询分析器中一般都是2秒左右,在系统中有时候很快,有时候却很慢超时查不出数据来。不知道是怎么回事
from(
select linkid,mobile,mo.matchcode,mocontent,motime,spcode,stat,Province state,company,ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
from t_jbxs_mo_log mo,t_mobile_zipcode mobi,t_jbxs_matchinfo matc
where substring(mo.mobile,0,8)=substring(mobi.Region,0,8) and matc.matchcode=mo.matchcode ) moo left join t_jbxs_mt_log mt on mt.linkid=moo.linkid where RowNumber>(100-1)*20 ) resultOrder order by motime desc
select * from(
select top 20 * from (
你这两层没法去重复呀..
把判断啥的放在程序里去执行,要不然总是这样,数据库受不了啊
也可以发现时间究竟是花在执行上,还是其他方面
联表查条件还是substring(mo.mobile,0,8)=substring(mobi.Region,0,8)
FROM
(SELECT top 20 *
FROM
(SELECT moo.linkid ,
moo.mobile ,
moo.matchcode ,
mocontent ,
motime ,
moo.spcode ,
moo.stat ,
moo.state ,
moo.company ,
mt.linkid mtlinkid,
mtcontent ,
mttime ,
mt.stat mtstat ,
RowNumber
FROM
(SELECT linkid ,
mobile ,
mo.matchcode ,
mocontent ,
motime ,
spcode ,
stat ,
Province state,
company ,
ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
FROM t_jbxs_mo_log mo ,
t_mobile_zipcode mobi,
t_jbxs_matchinfo matc
WHERE substring(mo.mobile,0,8)=substring(mobi.Region,0,8)
AND matc.matchcode =mo.matchcode
) moo
LEFT JOIN t_jbxs_mt_log mt
ON mt.linkid=moo.linkid
) result
WHERE RowNumber>(100-1)*20
) resultOrder
ORDER BY motime DESC帮楼主排了下版
这个是固定的值么,如果是最好大于该值,不要用表达式在计算该值。
http://www.cnblogs.com/bluedoctor/archive/2011/03/04/1970866.html