SELECT orgnid,
orgnno,
orgnname
INTO #a001orgn
FROM a001orgn
WHERE a001orgn.orgnno LIKE @OrgnNO + '%'SELECT accgyearid,
acctid,
pacctid,
acctno,
acctname,
nolevel,
islowestlevel,
balancedirection
INTO #a111acctitle
FROM a111acctitle
WHERE acctno LIKE @AcctNo + '%'
ORDER BY acctno --将数据插入临时表还要order by干嘛!?需要使用排序数据的时候,对#a111acctitle排序不应该在这里。这句去掉。SELECT #a001orgn.orgnno,
#a001orgn.orgnname,
#a111acctitle.acctno,
#a111acctitle.acctname,
Cast(0 AS money) AS ncye, --Cast(0 AS money) 声明为一个变量,下面的 Cast(0 AS money) 都使用这个变量。
Cast(0 AS money) AS qcye,
Cast(0 AS money) AS qmye,
Cast(0 AS money) AS bqjf,
Cast(0 AS money) AS bqdf,
Cast(0 AS money) AS bnlj,
Cast(0 AS money) AS bnld,
Cast(0 AS money) AS qmjf,
Cast(0 AS money) AS qmdf,
Cast(0 AS money) AS xjjf,
Cast(0 AS money) AS xjdf,
#a111acctitle.nolevel,
#a111acctitle.islowestlevel
INTO #orgnacct
FROM #a001orgn,
a110accg,--没有在select中有引用的字段,建议使用 in方式判断,而不是join方式。这么join容易产生冗余数据。
a111accgyear,--同上。
#a111acctitle
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = #a111acctitle.accgyearid
AND a111accgyear.fiscalyear = @FiscalYear
ORDER BY #a001orgn.orgnno, --同上。去掉!
#a111acctitle.acctno--1、下面这句会很慢。建议将其中的SELECT #a001orgn.orgnno结果部分放到临时表中,然后用这个临时表再与#orgnacct进行关联更新。速度提升或下降和你的实际数据有关,强烈建议你试试。
--2、SELECT #a001orgn.orgnno语句中除了#a001orgn、a111acctitle、#a111acctitle、a111voucherentry这四个表作inner join,其他的都关联后放在in中。
UPDATE #orgnacct
SET ncye = v1.ncye
FROM (SELECT #a001orgn.orgnno,
a111acctitle.acctno,
Isnull(Sum((1 - #a111acctitle.balancedirection * 2) * (a111voucherentry.bookrdmoney - a111voucherentry.bookrcmoney)),
0) AS ncye
FROM #a001orgn,
a110accg,
a111accgyear,
a111accperiod,
#a111acctitle,
a111acctitle,
a111voucher,
a111voucherentry
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = a111accperiod.accgyearid
AND a111voucher.accpid = a111accperiod.accpid
AND a111voucher.voucherid = a111voucherentry.voucherid
AND a111voucherentry.acctid = #a111acctitle.acctid
AND (a111acctitle.accgyearid = #a111acctitle.accgyearid
AND (a111acctitle.acctno = #a111acctitle.acctno
OR #a111acctitle.acctno LIKE a111acctitle.acctno + '-%'))
AND (Isnull(a111voucher.vouchersortid,'') = ''
OR a111voucher.vouchersorton = 0)
AND a111accgyear.fiscalyear = @FiscalYear
AND #a111acctitle.acctno LIKE @AcctNO + '%'
GROUP BY orgnno,
a111acctitle.acctno) v1
WHERE #orgnacct.orgnno = v1.orgnno
AND v1.acctno = #orgnacct.acctno
至于索引,不知道你的表数据情况,请根据实际的执行计划自行建立吧。
orgnno,
orgnname
INTO #a001orgn
FROM a001orgn
WHERE a001orgn.orgnno LIKE @OrgnNO + '%'SELECT accgyearid,
acctid,
pacctid,
acctno,
acctname,
nolevel,
islowestlevel,
balancedirection
INTO #a111acctitle
FROM a111acctitle
WHERE acctno LIKE @AcctNo + '%'
ORDER BY acctno --将数据插入临时表还要order by干嘛!?需要使用排序数据的时候,对#a111acctitle排序不应该在这里。这句去掉。SELECT #a001orgn.orgnno,
#a001orgn.orgnname,
#a111acctitle.acctno,
#a111acctitle.acctname,
Cast(0 AS money) AS ncye, --Cast(0 AS money) 声明为一个变量,下面的 Cast(0 AS money) 都使用这个变量。
Cast(0 AS money) AS qcye,
Cast(0 AS money) AS qmye,
Cast(0 AS money) AS bqjf,
Cast(0 AS money) AS bqdf,
Cast(0 AS money) AS bnlj,
Cast(0 AS money) AS bnld,
Cast(0 AS money) AS qmjf,
Cast(0 AS money) AS qmdf,
Cast(0 AS money) AS xjjf,
Cast(0 AS money) AS xjdf,
#a111acctitle.nolevel,
#a111acctitle.islowestlevel
INTO #orgnacct
FROM #a001orgn,
a110accg,--没有在select中有引用的字段,建议使用 in方式判断,而不是join方式。这么join容易产生冗余数据。
a111accgyear,--同上。
#a111acctitle
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = #a111acctitle.accgyearid
AND a111accgyear.fiscalyear = @FiscalYear
ORDER BY #a001orgn.orgnno, --同上。去掉!
#a111acctitle.acctno--1、下面这句会很慢。建议将其中的SELECT #a001orgn.orgnno结果部分放到临时表中,然后用这个临时表再与#orgnacct进行关联更新。速度提升或下降和你的实际数据有关,强烈建议你试试。
--2、SELECT #a001orgn.orgnno语句中除了#a001orgn、a111acctitle、#a111acctitle、a111voucherentry这四个表作inner join,其他的都关联后放在in中。
UPDATE #orgnacct
SET ncye = v1.ncye
FROM (SELECT #a001orgn.orgnno,
a111acctitle.acctno,
Isnull(Sum((1 - #a111acctitle.balancedirection * 2) * (a111voucherentry.bookrdmoney - a111voucherentry.bookrcmoney)),
0) AS ncye
FROM #a001orgn,
a110accg,
a111accgyear,
a111accperiod,
#a111acctitle,
a111acctitle,
a111voucher,
a111voucherentry
WHERE #a001orgn.orgnid = a110accg.orgnid
AND a110accg.accgid = a111accgyear.accgid
AND a111accgyear.accgyearid = a111accperiod.accgyearid
AND a111voucher.accpid = a111accperiod.accpid
AND a111voucher.voucherid = a111voucherentry.voucherid
AND a111voucherentry.acctid = #a111acctitle.acctid
AND (a111acctitle.accgyearid = #a111acctitle.accgyearid
AND (a111acctitle.acctno = #a111acctitle.acctno
OR #a111acctitle.acctno LIKE a111acctitle.acctno + '-%'))
AND (Isnull(a111voucher.vouchersortid,'') = ''
OR a111voucher.vouchersorton = 0)
AND a111accgyear.fiscalyear = @FiscalYear
AND #a111acctitle.acctno LIKE @AcctNO + '%'
GROUP BY orgnno,
a111acctitle.acctno) v1
WHERE #orgnacct.orgnno = v1.orgnno
AND v1.acctno = #orgnacct.acctno
至于索引,不知道你的表数据情况,请根据实际的执行计划自行建立吧。
解决方案 »
- 关于函数的一个问题。
- sqlserver2005怎么转换成access数据库?
- 关于查询分析器中分号的问题,谢谢
- Query->Edit(),报错,搞定另外开贴送100分
- 向专家们请教一个SQL问题!
- 想学SQL,烦请各位推荐一些好书
- DBCC CHECKDB的错误
- 请教个问题,我为一个软件做个外挂,在原sql server的表里做个很简单的触发器,为什么会导致原软件运行不正常呢?急!!!!!!
- Select into 只能加到新表中吗?
- 关于vb做分组报表的问题(急)
- 关于错误"Procedure has not been executed or has no results"
- 下面的SQL语句输出的结果是什么?
谢谢你的指导与帮助