数据库为SQLServer 2000
先看下原来的查询
- SQL code
SELECT buhuo = CASE WHEN b.slqty > a.qty THEN '*' ELSE '' END, c.PLUno, c.pluname,mll = (c.slprc - c.csprc) / c.slprc * 100, c.csprc, c.slprc, a.QTY, kcje = a.qty * c.csprc, b.slqty, slamt = b.slqty * c.slprc, zck = d.ckqtyFROM (SELECT pluid = a.pluid, pluno = a.pluno, pluname = a.pluname, csprc = b.avgcsprc, slprc = b.slprc FROM basplumain a, baspluprc b WHERE a.pluid = b.pluid AND a.pluno LIKE :PLUNO + '%' group by a.pluid) c JOIN (SELECT PLUID = a.pluid, QTY = SUM(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a WHERE shpid = 2 GROUP BY pluid) a ON a.pluid = c.pluid LEFT JOIN (SELECT slQTY = SUM(QTY), pluid FROM FINEBK WHERE DOCTYP / 1000000 = 21 AND level3 = 0 AND docdat >= :BDAT AND docdat <= :EDAT GROUP BY pluid) b ON c.pluid = b.pluid LEFT JOIN (SELECT pluid, ckQTY = a.BEGQTY + a.INQTY - a.OUTQTY FROM (SELECT PLUID, BEGCS = SUM(BEGCS), BEGQTY = SUM(BEGQTY), INCS = SUM(INCS), INQTY = SUM(INQTY), OUTQTY = SUM(OUTQTY) FROM FINSTOCKSHP A WHERE a.shpid = 7 GROUP BY PLUID) A) d ON c.pluid = d .pluid
其中 :BDAT即为 当月开始日期,即 '2012-02-01'
:EDAT即为 当月结束日期,即 '2012-02-29'
:PLUNO 是字符型常量,客户端允许为空,为空即查询通配符 '%'
现增加两个字段,
代码如下
- SQL code
SELECT buhuo = CASE WHEN b.slqty > a.qty THEN '*' ELSE '' END, c.PLUno, c.pluname, c.pkunit, c.spec, mll = (c.slprc - c.csprc) / c.slprc * 100, c.csprc, c.slprc, a.QTY, kcje = a.qty * c.csprc, b.slqty, slamt = b.slqty * c.slprc, zck = d.ckqtyFROM (SELECT pluid = a.pluid, pluno = a.pluno, pluname = a.pluname, spec = a.spec, pkunit = a.pkunit, csprc = b.avgcsprc, slprc = b.slprc FROM basplumain a, baspluprc b WHERE a.pluid = b.pluid AND a.pluno LIKE :PLUNO + '%' group by a.pluid) c JOIN (SELECT PLUID = a.pluid, QTY = SUM(a.BEGQTY + a.INQTY - a.OUTQTY) FROM FINSTOCKSHP a WHERE shpid = 2 GROUP BY pluid) a ON a.pluid = c.pluid LEFT JOIN (SELECT slQTY = SUM(QTY), pluid FROM FINEBK WHERE DOCTYP / 1000000 = 21 AND level3 = 0 AND docdat >= :BDAT AND docdat <= :EDAT GROUP BY pluid) b ON c.pluid = b.pluid LEFT JOIN (SELECT pluid, ckQTY = a.BEGQTY + a.INQTY - a.OUTQTY FROM (SELECT PLUID, BEGCS = SUM(BEGCS), BEGQTY = SUM(BEGQTY), INCS = SUM(INCS), INQTY = SUM(INQTY), OUTQTY = SUM(OUTQTY) FROM FINSTOCKSHP A WHERE a.shpid = 7 GROUP BY PLUID) A) d ON c.pluid = d .pluid
增加了 spec,pkunit,两个字段均来自于basplumain表中
但,增加了两个字段后,客户端查询通配符时 超慢, 去掉两个字段就好了....汗...
另外,在查询分析器中却没有这么诡异...哎...
实在不懂到底哪里错了,求各位大大帮忙!
或者,这段SQL应该如何优化? 代码中我是否有不规范的地方?
------解决方案--------------------
SELECT pluid = a.pluid, pluno = a.pluno, pluname = a.pluname, spec = a.spec,
pkunit = a.pkunit, csprc = b.avgcsprc, slprc = b.slprc
FROM basplumain a, baspluprc b
WHERE a.pluid = b.pluid AND a.pluno LIKE :PLUNO + '%'
group by a.pluid
这一段应该有错误，或者你根本贴的不是原代码
------解决方案--------------------
------解决方案--------------------