select * from(select ROW_NUMBER() OVER (ORDER BY reverttime desc) AS rownum,R.cardid,R.moduleid,cardtheme,cardperson,T.revertcontent,T.revertperson,T.reverttime,W.cardnum,H.Hid,G.Gid from Card R
left join(select revertid,cardid,revertcontent,revertperson,reverttime from Reverts Y where not exists(select 1 from Reverts where cardid=Y.cardid and revertid>Y.revertid ))T on R.cardid=T.cardid
left join(select cardid,count(*) as cardnum from Reverts group by cardid)W on R.cardid=W.cardid
left join(select id as Hid,username from UserInfoTable)H on R.cardperson=H.username
left join(select id as Gid,username from UserInfoTable)G on T.revertperson=G.username
where R.moduleid=cast(@moduleid as nvarchar(50))) as TEMPRESULT
where rownum between str((@currentpage-1)*@pagesize)+1 and str(@currentpage*@pagesize)从第二个left join开始,都只是想取一行数据,比如第二个left join取回复总数,第三个取发帖人id,第四个left join取回帖人id,都是和Card表连接,感觉这样left join效率不高。我想select * from(select ROW_NUMBER() OVER (ORDER BY reverttime desc) AS rownum,R.cardid,R.moduleid,cardtheme,cardperson,T.revertcontent,T.revertperson,T.reverttime,W.cardnum,H.Hid,G.Gid from Card R
left join(select revertid,cardid,revertcontent,revertperson,reverttime from Reverts Y where not exists(select 1 from Reverts where cardid=Y.cardid and revertid>Y.revertid ))T on R.cardid=T.cardid
where R.moduleid=cast(@moduleid as nvarchar(50))) as TEMPRESULT
where rownum between str((@currentpage-1)*@pagesize)+1 and str(@currentpage*@pagesize)
这结果作为一个临时表
然后再left join W/H/G,这样可行不,本人没用过临时表,请教下前辈
select * from(select ROW_NUMBER() OVER (ORDER BY reverttime desc) AS rownum,R.cardid,R.moduleid,cardtheme,cardperson,T.revertcontent,T.revertperson,T.reverttime,W.cardnum,H.Hid,G.Gid
from Card R
left join(
select revertid,cardid,revertcontent,revertperson,reverttime
from Reverts Y
where not exists
(
select 1 from
Reverts where cardid=Y.cardid and revertid>Y.revertid
)
)T on R.cardid=T.cardid
left join(
select cardid,count(*) as cardnum
from Reverts group by cardid
)W on R.cardid=W.cardid
left join(
select id as Hid,username
from UserInfoTable
)H on R.cardperson=H.username
left join(
select id as Gid,username
from UserInfoTable
)G on T.revertperson=G.username
where R.moduleid=cast(@moduleid as nvarchar(50))
) as TEMPRESULT
where rownum between str((@currentpage-1)*@pagesize)+1 and str(@currentpage*@pagesize)
from(select ROW_NUMBER() OVER (ORDER BY reverttime desc) AS rownum,
R.cardid,R.moduleid,cardtheme,cardperson,
T.revertcontent,T.revertperson,T.reverttime,W.cardnum,
H.id,G.id
from Card R
left join(
select y.revertid,y.cardid,y.revertcontent,y.revertperson,y.reverttime
from Reverts Y
inner join
(
select cardid,
MIN(revertid) revertid --最小的回复id
from Reverts
where cardid=Y.cardid
group cardid
) t
on t.cardid = y.cardid
and t.revertid = y.revertid
)T on R.cardid=T.cardid
left join(
select cardid,
count(*) as cardnum
from Reverts
group by cardid
)W on R.cardid=W.cardid
left join UserInfoTable
on R.cardperson=H.username
left join UserInfoTable
on T.revertperson=G.username
where R.moduleid=cast(@moduleid as nvarchar(50))
) as TEMPRESULT
where rownum between str((@currentpage-1)*@pagesize)+1 and str(@currentpage*@pagesize)