请教如下语句还能怎么优化吗???

vfp_database 2002-09-08 10:19:00
select khbm,khmc,blbm,blmc,blys,bldw,sum(a_ps) as a_ps,sum(a_sl) as a_sl,
sum(b_ps) as b_ps,sum(b_sl) as b_sl,sum(a_sl)-sum(b_sl) as ab_sl,
sum(c_ps) as c_ps,sum(c_ps) as c_ps,sum(c_sl) as c_sl,sum(b_sl)-sum(c_sl) as bc_sl
from (
SELECT A.KHBM AS KHBM,A.KHMC AS KHMC,A1.BLBM AS BLBM,A1.BLMC AS BLMC,
A1.BLYS AS BLYS,A1.BLDW AS BLDW,SUM(A1.PS) AS A_PS,SUM(A1.SL) AS A_SL,1 AS QB,
0 AS B_PS,0 AS B_SL,0 AS C_PS,0 AS C_SL
FROM BLJCD A,BLJCDMX A1 WHERE A.DH=A1.DH
GROUP BY A.KHBM,A.KHMC,A1.BLBM,A1.BLMC,A1.BLYS,A1.BLDW
UNION ALL
SELECT B.KHBM AS KHBM,B.KHMC AS KHMC,B.BLBM AS BLBM,B.BLMC AS BLMC,B.BLYS AS BLYS,
B1.BLDW AS BLDW,0 AS A_PS,0 AS A_SL,2 AS QB,
SUM(B.PS_H) AS B_PS,SUM(B.SL_H) AS B_SL,0 AS C_PS,0 AS C_SL
FROM BLCPJCD B,BLCPJCDMX B1 WHERE B.ID=B1.ID
GROUP BY B.KHBM,B.KHMC,B.BLBM,B.BLMC,B.BLYS,B1.BLDW
UNION ALL
SELECT C.KHBM AS KHBM,C.KHMC AS KHMC,C1.BLBM AS BLBM,C1.BLMC AS BLMC,C1.BLYS AS BLYS,
C1.BLDW AS BLDW,0 AS A_PS,0 AS A_SL ,3 AS QB,
0 AS B_PS,0 AS B_SL,
SUM(C1.PS) AS A_PS,SUM(C1.SL) AS A_SL
FROM BLCCD C,BLCCDMX C1 WHERE C.DHS=C1.DHS
GROUP BY C.KHBM,C.KHMC,C1.BLBM,C1.BLMC,C1.BLYS,C1.BLDW ) s group by s.khbm,s.khmc,s.blbm,s.blmc,s.blys,s.bldw
...全文
37 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
chenybin 2002-12-08
  • 打赏
  • 举报
回复
:)
dotnba 2002-12-08
  • 打赏
  • 举报
回复
:)
lyyrw 2002-11-28
  • 打赏
  • 举报
回复
select khbm,khmc,blbm,blmc,blys,bldw,sum(a_ps) as a_ps,sum(a_sl) as a_sl,
sum(b_ps) as b_ps,sum(b_sl) as b_sl,sum(a_sl)-sum(b_sl) as ab_sl,
sum(c_ps) as c_ps,sum(c_ps) as c_ps,sum(c_sl) as c_sl,sum(b_sl)-sum(c_sl) as bc_sl
from (
SELECT A.KHBM AS KHBM,A.KHMC AS KHMC,A1.BLBM AS BLBM,A1.BLMC AS BLMC,
A1.BLYS AS BLYS,A1.BLDW AS BLDW,A1.PS AS A_PS,A1.SL AS A_SL,1 AS QB,
0 AS B_PS,0 AS B_SL,0 AS C_PS,0 AS C_SL
FROM BLJCD A,BLJCDMX A1 WHERE A.DH=A1.DH
UNION ALL
SELECT B.KHBM AS KHBM,B.KHMC AS KHMC,B.BLBM AS BLBM,B.BLMC AS BLMC,B.BLYS AS BLYS,
B1.BLDW AS BLDW,0 AS A_PS,0 AS A_SL,2 AS QB,
B.PS_H AS B_PS,B.SL_H AS B_SL,0 AS C_PS,0 AS C_SL
FROM BLCPJCD B,BLCPJCDMX B1 WHERE B.ID=B1.ID
UNION ALL
SELECT C.KHBM AS KHBM,C.KHMC AS KHMC,C1.BLBM AS BLBM,C1.BLMC AS BLMC,C1.BLYS AS BLYS,
C1.BLDW AS BLDW,0 AS A_PS,0 AS A_SL ,3 AS QB,
0 AS B_PS,0 AS B_SL,
C1.PS AS A_PS,C1.SL AS A_SL
FROM BLCCD C,BLCCDMX C1 WHERE C.DHS=C1.DHS
) s
group by s.khbm,s.khmc,s.blbm,s.blmc,s.blys,s.bldw
wshlxvb 2002-11-28
  • 打赏
  • 举报
回复
把你的数据库结构写一下。
thank you
CrazyFor 2002-11-28
  • 打赏
  • 举报
回复
太复杂了!我没有耐心看。:)
vfp_database 2002-09-18
  • 打赏
  • 举报
回复
up
lj77 2002-09-11
  • 打赏
  • 举报
回复
您的SQL语句中含了两层SQL,我的意见是将外层的sum写到里层去,可以有效减少外层中间结果的记录数,可以提高一点性能。

其实,仔细观察你的SQL语句,发现你是希望从三组表中取得某些字段间的关系值,如果对于三组表的关键字段(khbm,khmc,blbm,blmc,blys,bldw)的值域是包含关系,即有一组表中的(khbm,khmc,blbm,blmc,blys,bldw)字段记录组合函盖了其他两组表,倒可以使用内联表来提高效能,否则像这样的查询还是写成存储过程可以得到更高的效能!
================================================================

CSDN 论坛助手 Ver 1.0 B0402提供下载。 改进了很多,功能完备!

★ 浏览帖子速度极快![建议系统使用ie5.5以上]。 ★ 多种帖子实现界面。
★ 保存帖子到本地[html格式]★ 监视您关注帖子的回复更新。
★ 可以直接发贴、回复帖子★ 采用XML接口,可以一次性显示4页帖子,同时支持自定义每次显示帖子数量。可以浏览历史记录!
★ 支持在线检测程序升级情况,可及时获得程序更新的信息。

★★ 签名 ●
可以在您的每个帖子的后面自动加上一个自己设计的签名哟。

Http://www.ChinaOK.net/csdn/csdn.zip
Http://www.ChinaOK.net/csdn/csdn.rar
Http://www.ChinaOK.net/csdn/csdn.exe [自解压]

Foxer 2002-09-11
  • 打赏
  • 举报
回复
不一定啊,如果吃饭的时候饭不热而汤很热,而等吃完饭汤就凉下来了,这样先吃饭后喝汤时间就会短一些了
vfp_database 2002-09-11
  • 打赏
  • 举报
回复
to:Foxer(狐狸)

嘿嘿。。。哈哈哈。。。。
vfp_database 2002-09-11
  • 打赏
  • 举报
回复
to:liujianjun_(流星尔)
好象没有多大的用处吧?

好象只是先吃饭再喝烫,
还是先喝烫再吃饭。我想没有什么用处吧。
流星尔 2002-09-09
  • 打赏
  • 举报
回复
这样不知道能不能快一点。
流星尔 2002-09-09
  • 打赏
  • 举报
回复
把所有子查询里的sum和group by先去掉.然后在最外层sum,group by

34,575

社区成员

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

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