select PP0104,dlxs,lyxs
from
(select PP0104,dlxs
from PP001 left join (select PP0104 dl,sum(XS0504*XS0505) dlxs
from PP001,XS005
where PP0101=XS0501 and XS0502=(select to_char(sysdate-1,'yyyymmdd') from dual)
group by PP0104)
on PP0104=dl
group by PP0104,dlxs)left join
(select ds0311,sum(sly0103) lyxs
from ds003,sly001
where ds0301=sly0102 and sly0101=(select to_char(sysdate-1,'yyyymmdd') from dual)
group by ds0311)
on PP0104=ds0311
order by PP0104各位大虾,小弟我运行以上得出以下结果:DG0104 DLXS LYXS
A 2885.91 99
B
C 228.9
D 7.9
E 251.11
F 2603.16 1611.7但现在有个问题就是ds0311这个表里有个Q,但是PP0104里没有,所以取出的数据没有体现Q,请教各位,我想得出以下结果该怎么写语句,谢谢大家的帮忙了DG0104 DLXS LYXS
A 2885.91 99
B
C 228.9
D 7.9
E 251.11
F 2603.16 1611.7
Q 559.6
from
(select PP0104,dlxs
from PP001 left join (select PP0104 dl,sum(XS0504*XS0505) dlxs
from PP001,XS005
where PP0101=XS0501 and XS0502=(select to_char(sysdate-1,'yyyymmdd') from dual)
group by PP0104)
on PP0104=dl
group by PP0104,dlxs)left join
(select ds0311,sum(sly0103) lyxs
from ds003,sly001
where ds0301=sly0102 and sly0101=(select to_char(sysdate-1,'yyyymmdd') from dual)
group by ds0311)
on PP0104=ds0311
order by PP0104各位大虾,小弟我运行以上得出以下结果:DG0104 DLXS LYXS
A 2885.91 99
B
C 228.9
D 7.9
E 251.11
F 2603.16 1611.7但现在有个问题就是ds0311这个表里有个Q,但是PP0104里没有,所以取出的数据没有体现Q,请教各位,我想得出以下结果该怎么写语句,谢谢大家的帮忙了DG0104 DLXS LYXS
A 2885.91 99
B
C 228.9
D 7.9
E 251.11
F 2603.16 1611.7
Q 559.6
这样子才知道怎么帮你,这个数据写出来,我们也不懂表的数据到底什么情况!
你可以试试union 把结果集连在一起
SELECT PP0104,DLXS,LYXS
FROM PP001 P1,
(SELECT PP0104,SUM(XS0504*XS0505) DLXS
FROM PP001, XS005
WHERE PP0101 = XS0501
AND XS0502 = TO_CHAR(SYSDATE-1,'YYYYMMDD')
GROUP BY PP0104
) X1,
(SELECT DS0311,SUM(SLY0103) LYXS
FROM DS003, SLY001
WHERE DS0301 = SLY0102
AND SLY0101 = TO_CHAR(SYSDATE-1,'YYYYMMDD')
GROUP BY DS0311
) D1
WHERE P1.PP0104 = X1.PP0104(+)
AND P1.PP0104 = D1.DS0311(+)
ORDER BY P1.PP0104
UNION
--SELECT PP0104,DLXS,LYXS
--FROM DS003;
然后把开头select 后的那个PP0104换成
decode(PP0104,null,ds0311,pp0104) PP0104
from
(select PP0104,a1.dlxs
from PP001 left join (select pp001.PP0104,sum(xs005.XS0504*xs005.XS0505) dlxs
from PP001,XS005
where pp001.PP0101=xs005.XS0501 and xs005.XS0502=to_char(sysdate-1,'yyyymmdd')
group by pp001.PP0104) a1
using(PP0104)
group by PP0104,a1.dlxs) afull join
(select ds003.ds0311,sum(sly001.sly0103) lyxs
from ds003 left join sly001
on ds003.ds0301=sly001.sly0102 and sly001.sly0101=to_char(sysdate-1,'yyyymmdd')
group by ds003.ds0311) b
on a.PP0104=b.ds0311
order by a.PP0104
你再看看,也许是ds003和sly001关联的时候被过滤掉了
from
(select PP0104,a1.dlxs
from PP001 left join (select pp001.PP0104,sum(xs005.XS0504*xs005.XS0505) dlxs
from PP001,XS005
where pp001.PP0101=xs005.XS0501 and xs005.XS0502=to_char(sysdate-1,'yyyymmdd')
group by pp001.PP0104) a1
using(PP0104)
group by PP0104,a1.dlxs) afull join
(select ds003.ds0311,sum(sly001.sly0103) lyxs
from ds003 left join sly001
on ds003.ds0301=sly001.sly0102 and sly001.sly0101=to_char(sysdate-1,'yyyymmdd')
group by ds003.ds0311) b
on a.PP0104=b.ds0311
order by a.PP0104 你再看看,也许是ds003和sly001关联的时候被过滤掉了
没有报错了,但是就是运算不出类,是不是算法不对??请指教!~
没有查出数据,还是计算结果不对
我这里没有数据没法测试,你试试把两个查询子句分别执行一次
看看是哪一块的问题
......
把这个改下,最后的order by a.PP0104
改成order by pp0104
a.PP0104=b.ds0311
看看上下两部分查询结果能不能关联上
还有检查下数据类型是否一致
检查下pp0104和ds0311的取值,是否有空值或者有重复导致笛卡尔积的
总之应该是两表之间的关联效率太低
抛开速度,运行的结果符合你的要求吗?left join没问题的话
a.PP0104,a.dlxs,b.lyxs
from
(select PP0104,a1.dlxs
from PP001 left join (select pp001.PP0104,sum(xs005.XS0504*xs005.XS0505) dlxs
from PP001,XS005
where pp001.PP0101=xs005.XS0501 and xs005.XS0502=to_char(sysdate-1,'yyyymmdd')
group by pp001.PP0104) a1
using(PP0104)
group by PP0104,a1.dlxs) aleft join
(select ds003.ds0311,sum(sly001.sly0103) lyxs
from ds003 left join sly001
on ds003.ds0301=sly001.sly0102 and sly001.sly0101=to_char(sysdate-1,'yyyymmdd')
group by ds003.ds0311) b
on a.PP0104=b.ds0311
order by a.PP0104
union
(select ds0311,null,lyxs from
(select ds003.ds0311,sum(sly001.sly0103) lyxs
from ds003 left join sly001
on ds003.ds0301=sly001.sly0102 and sly001.sly0101=to_char(sysdate-1,'yyyymmdd')
group by ds003.ds0311) where ds0311 not in
(select distinct PP0104
from PP001
)
)看看要多长时间
但是用left join的话,有个数据都体现不出来,必须把两张表的数据都算出来,哎
晕死!~
已经把full join 替换掉了
而且你说的那个数据应该会出现在结果里
SELECT nvl(pp0104,ds0311), dlxs, lyxs
FROM (SELECT pp0104 , SUM(xs0504 * xs0505) dlxs
FROM pp001, xs005
WHERE pp0101 = xs0501(+)
AND xs0502 = to_char(SYSDATE - 1, 'yyyymmdd')
GROUP BY pp0104)
FULL JOIN
(SELECT ds0311, SUM(sly0103) lyxs
FROM ds003, sly001
WHERE ds0301 = sly0102
AND sly0101 = to_char(SYSDATE - 1, 'yyyymmdd')
GROUP BY ds0311)
ON pp0104 = ds0311
ORDER BY pp0104;
wildwave,非常感谢你的回复,但是还是很慢,
我用"beyondme6"回复的试了一下,很快就运行出来了,谢谢beyondme6