查询在 Management Studio 中有效，但在客户端中无效

Question

这是一个查询 -

select  maclin,mamatn,caname,madesc,madtop,malstd,mastat,ISNULL(sum(tlchgv),0.00) as WIP,
    (select isnull(sum(blcost),0.00) from blfile where blclin = maclin and blmatn = mamatn) 
    as billed,
    isnull(rfhghq,0.00),isnull(rffixq,0.00),ISNULL(rfdate,'17770101'),cmidst
      from mafile,cafile,tlfile,rffile,cmfile where maclin=caclin and maclin*=tlclin 
      and mamatn*=tlmatn and
      maclin*=rfclin and mamatn*=rfmatn and maclin=cmclin and tlstat='' and
      maeact = 32 and maspca = 0
      group by maclin,mamatn,caname,madesc,madtop,malstd,mastat,rfhghq,rffixq,rfdate,cmidst
      order by caname,maclin,mamatn

如果我在 SQL Server Management Studio 上运行，该查询将在 1 秒内运行并返回 5190 行。

当我通过 ODBC 从 Windows XP PC 桌面上的 Visual Foxpro 程序运行完全相同的查询时，查询没有返回错误，但没有返回行！

如果我将 select top 5000 添加到查询中，它可以工作，但需要 5 分钟。如果我加注选择前 5200 名，它会再次返回任何内容。

使用与 SQL 2000 相同的 Visual Foxpro 程序，它工作得很好。

最奇怪的是。有人对可能出现的问题有任何想法吗？

Answer 1

让我们看看现代语法是否仍然能够获得您想要的结果，而无需花费很长时间。

SELECT ma.maclin, ma.mamatn, ca.caname, ma.madesc, ma.madtop, ma.malstd, 
    WIP    = COALESCE(SUM(tl.tlchgv), 0.00),
    billed = COALESCE(SUM(bl.blcost), 0.00),
    rfhghq = COALESCE(rf.rfhghq, 0.00),
    rffixq = COALESCE(rf.rffizq, 0.00),
    rfdate = COALESCE(rf.rfdate, '17770101'),
    cm.cmidst
FROM dbo.mafile AS ma
INNER JOIN dbo.cafile AS ca
  ON ma.maclin = ca.caclin
INNER JOIN dbo.cmfile AS cm
  ON ma.maclin = cm.cmclin
LEFT OUTER JOIN dbo.tlfile AS tl
  ON ma.maclin = tl.tlclin
  AND ma.mamatn = tl.tlmatn
  AND tl.tlstat = ''
LEFT OUTER JOIN dbo.rffile AS rf
  ON ma.mamatn = rf.rfmatn
LEFT OUTER JOIN dbo.blfile AS bl
  ON bl.blclin = ma.maclin 
  AND bl.blmatn = ma.mamatn
WHERE 
   ma.maeact = 32 AND ma.maspca = 0
GROUP BY 
   ma.maclin, ma.mamatn, ca.caname, ma.madesc, ma.madtop, ma.malstd,
   COALESCE(rf.rfhghq, 0.00),
   COALESCE(rf.rffizq, 0.00),
   COALESCE(rf.rfdate, '17770101'),
   cm.cmidst
ORDER BY ca.caname, ma.maclin, ma.mamatn;