请教一个取代游标的SQL
这是BT算法的一部分:
SELECT F.Name,F.[FileName],P.PID,P.IP,P.Port2 FROM
(
SELECT Name,[FileName] FROM BTFiles WHERE InfoHash='D9CAB934C6D9AD48462B7BE0EF06C0B243027A53'
) F,
(
SELECT Top 20 PID,IP,Port2 FROM
(
SELECT DISTINCT PID,IP,Port2,MaxSpeed FROM Peers WITH (NOLOCK)
WHERE PID IN (SELECT PID FROM V_BTUsersDownloadFiles WITH (NOLOCK) WHERE InfoHash='D9CAB934C6D9AD48462B7BE0EF06C0B243027A53' and Bytes=0 AND PID=Peers.PID)
AND PID NOT IN (SELECT PID FROM Peers WHERE InfoHash='D9CAB934C6D9AD48462B7BE0EF06C0B243027A53')
AND PID NOT IN (SELECT PID FROM BTReseedFast WHERE InfoHash='D9CAB934C6D9AD48462B7BE0EF06C0B243027A53' AND GradeCode<>99)
AND IsReseed=1
) P0
ORDER BY P0.MaxSpeed DESC
) P
现在有一个TAB_infohash表(InfoHash varcha(40)),想要加入进来并使输出中增加InfoHash,我不想用TAB_infohash表做游标,怎么修改这个SQL THX!
[解决办法]
try this,
SELECT F.Name,F.[FileName],P.PID,P.IP,P.Port2,F.InfoHash FROM
( SELECT a.Name,a.[FileName],a.InfoHash
FROM BTFiles a
inner join TAB_infohash b on a.InfoHash=b.InfoHash
) F
cross apply
( SELECT Top 20 PID,IP,Port2 FROM
(
SELECT DISTINCT PID,IP,Port2,MaxSpeed FROM Peers WITH (NOLOCK)
WHERE PID IN (SELECT PID FROM V_BTUsersDownloadFiles WITH (NOLOCK)
WHERE InfoHash=F.InfoHash and Bytes=0 AND PID=Peers.PID)
AND PID NOT IN (SELECT PID FROM Peers
WHERE InfoHash=F.InfoHash)
AND PID NOT IN (SELECT PID FROM BTReseedFast
WHERE InfoHash=F.InfoHash AND GradeCode<>99)
AND IsReseed=1
) P0
ORDER BY P0.MaxSpeed DESC) P