如何精简此SQL文?高手请进!

wangzhenzhu1975 2003-10-18 10:59:26
select b.officeid,b.budgetno,b.budgetitem,
(select sum(yearbudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as yearbudget,
(select sum(onebudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as onebudget,
(select sum(twobudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as twobudget,
(select sum(threebudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as threebudget,
(select sum(fourbudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as fourbudget,
(select sum(fivebudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as fivebudget,
(select sum(sixbudget) from budgetinfor where budgetno like rtrim(b.budgetno)+'%' and officeid='8888')
as sixbudget
from budgetinfor b
join financesubject f
on f.subjectid=b.budgetno
where officeid='8888'
and f.lastsubject='0'
...全文
29 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
wangzhenzhu1975 2003-10-19
  • 打赏
  • 举报
回复
谁有办法?
meteorlg 2003-10-19
  • 打赏
  • 举报
回复
如果速度很慢的话,试一下用临时表
先将数据插入临时表,需要sum的字段用0代替,然后对每个需要sum的字段执行update
wangzhenzhu1975 2003-10-18
  • 打赏
  • 举报
回复
是在同一张表里取,你没见吗?
Ouyangyifeiczy 2003-10-18
  • 打赏
  • 举报
回复
楼主这个问题表述不太清楚。JOIN financesubject 这个表数据好象都没用着,可以放在条件里啊。
另:那么多 sum(...) 如果如你所说,完全可以单独生成一张表,在处理啊
select sum(field1),sum(field2),.... from ... where budgetno like rtrim(b.budgetno)+'%' and officeid='8888'...

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧