执行没有结果,为何?

tianshibuhuifei 2011-09-05 01:45:09
SQL code
select (sum(b.WKGBTR)+sum(c.SALK3)) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRGNG='COIE' or b.VRGNG='COIN') and b.WRTTP='04'
left join QBEW c on c.PSPNR in (a.PSPNR) and c.MANDT='800' and c.BWKEY='NC01' and c.SOBKZ='Q'
group by a.PSPID



sqlserver2005,

现在情况就是执行这个语句,查半天都没有数据,半天的概念就是很久,几十分钟!所以个人觉得可能是有点问题,
但又不知道问题出在哪里?我把他们分开写

SQL code
select sum(b.WKGBTR) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRGNG='COIE' or b.VRGNG='COIN') and b.WRTTP='04'
group by a.PSPID



这样是可以挺快得到数据的
同样QBEW表也可以得到数据,
但不知为何合在一起就得不到数据,也不报错!

请教前辈们?是语句哪里写得不对吗?
...全文
15803 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
tianshibuhuifei 2011-09-05
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 chuanzhang5687 的回复:]
吧连接的b c 这两个表的数据放在临时表里 然后在和在一起 如何?

引用 10 楼 tianshibuhuifei 的回复:
引用 9 楼 chuanzhang5687 的回复:
V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR)
连接的后面换成等号试试


SQL code

select (sum(b.WKGBTR)+……
[/Quote]
这个方法可行,但是执行起来速度好慢的!
tianshibuhuifei 2011-09-05
  • 打赏
  • 举报
回复

V_FZR_CL

CREATE VIEW V_FZR_CL AS
select DISTINCT a.PSPID,a.POST1,a.VERNR,b.POSID,b.PSPNR,b.OBJNR,c.BANFN,c.BNFPO,d.EBELN,d.EBELP
from PROJ a
join PRPS b on b.PSPHI in (a.PSPNR) and b.MANDT='800' and b.IZWEK='05'
join EBKN c on c.PS_PSP_PNR in (b.PSPNR) and c.MANDT='800'
left join EKKN d on d.PS_PSP_PNR in (b.PSPNR) and d.MANDT='800'
where a.MANDT='800' and a.WERKS='NC01' and (a.PSPID like 'T%' or a.PSPID like 'M%') and a.PSPID like '__N%'

话说这句执行起来数据量是有点大 1w多条
chuanzhang5687 2011-09-05
  • 打赏
  • 举报
回复
吧连接的b c 这两个表的数据放在临时表里 然后在和在一起 如何?[Quote=引用 10 楼 tianshibuhuifei 的回复:]
引用 9 楼 chuanzhang5687 的回复:
V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR)
连接的后面换成等号试试


SQL code

select (sum(b.WKGBTR)+sum(c.SALK3)) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on……
[/Quote]
冷箫轻笛 2011-09-05
  • 打赏
  • 举报
回复
最终结果查的是汇总B,C两表的数据,所以这语句完全可以使用inner join,on 条件里用=

select (sum(b.WKGBTR)+sum(c.SALK3)) as FKBY,a.PSPID
from V_FZR_CL a
inner join COEP b on b.OBJNR = a.OBJNR and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRGNG='COIE' or b.VRGNG='COIN') and b.WRTTP='04'
inner join QBEW c on c.PSPNR = a.PSPNR and c.MANDT='800' and c.BWKEY='NC01' and c.SOBKZ='Q'
group by a.PSPID

如果这样查还是不行的话,问题应该出在V_FZR_CL 上了,因为试图关联表查询的话,并不是先查询视图结果,再关联。数据库会自动重新解析的。

tianshibuhuifei 2011-09-05
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 chuanzhang5687 的回复:]
V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR)
连接的后面换成等号试试


SQL code

select (sum(b.WKGBTR)+sum(c.SALK3)) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR =a.OBJNR and b……
[/Quote]

还是一样!纠结,到底什么问题?我前面几个用的同样的写法!
chuanzhang5687 2011-09-05
  • 打赏
  • 举报
回复
V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR)
连接的后面换成等号试试

select (sum(b.WKGBTR)+sum(c.SALK3)) as FKBY,a.PSPID  
from V_FZR_CL a
left join COEP b on b.OBJNR =a.OBJNR and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRGNG='COIE' or b.VRGNG='COIN') and b.WRTTP='04'
left join QBEW c on c.PSPNR =a.PSPNR and c.MANDT='800' and c.BWKEY='NC01' and c.SOBKZ='Q'
group by a.PSPID


[Quote=引用 5 楼 tianshibuhuifei 的回复:]
引用 3 楼 fredrickhu 的回复:
SQL code

select (sum(isnull(b.WKGBTR,0))+sum(isnull(c.SALK3,0))) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='10……
[/Quote]
tianshibuhuifei 2011-09-05
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 chuanzhang5687 的回复:]
V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR)
有这种连接用法吗?
[/Quote]
恩?那该怎么写?
chuanzhang5687 2011-09-05
  • 打赏
  • 举报
回复
V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR)

有这种连接用法吗?
CalvinR 2011-09-05
  • 打赏
  • 举报
回复
好长好长 观摩中……
tianshibuhuifei 2011-09-05
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code

select (sum(isnull(b.WKGBTR,0))+sum(isnull(c.SALK3,0))) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC0……
[/Quote]

我并不是执行后结果为空,我是一直在那边查询,查询,查询!但又没有结果显示,速度极慢
tianshibuhuifei 2011-09-05
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 chuanzhang5687 的回复:]
SQL code

select * from
(
select sum(b.WKGBTR) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRG……
[/Quote]
海盗兄,这是什么意思?
--小F-- 2011-09-05
  • 打赏
  • 举报
回复
select (sum(isnull(b.WKGBTR,0))+sum(isnull(c.SALK3,0))) as FKBY,a.PSPID  
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRGNG='COIE' or b.VRGNG='COIN') and b.WRTTP='04'
left join QBEW c on c.PSPNR in (a.PSPNR) and c.MANDT='800' and c.BWKEY='NC01' and c.SOBKZ='Q'
group by a.PSPID
chuanzhang5687 2011-09-05
  • 打赏
  • 举报
回复
看错了!
chuanzhang5687 2011-09-05
  • 打赏
  • 举报
回复
select * from 
(
select sum(b.WKGBTR) as FKBY,a.PSPID
from V_FZR_CL a
left join COEP b on b.OBJNR in (a.OBJNR) and b.MANDT='800' and b.KOKRS='1000' and b.WERKS='NC01'
and (b.VRGNG='COIE' or b.VRGNG='COIN') and b.WRTTP='04'
group by a.PSPID


)t

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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