涉及表(如图):
我的SQL:
select c.orgname,a.jndw ajndw,a.rs ars,a.grjfbl agrjfbl,a.dwhrbl adwhrbl,a.dwhr adwhr,a.jfjs ajfjs,a.grjn agrjn,b.jndw bjndw,b.rs brs,b.grjfbl bgrjfbl,b.dwhrbl bdwhrbl,b.jfjs bjfjs,b.grjn bgrjn,b.dwhr bdwhr
from
(select jndw,grjfbl,dwhrbl,count(*) rs,nvl(sum(jfjs),0) jfjs,nvl(sum(grjn),0) grjn,nvl(sum(dwhr),0) dwhr from sdbx_ylbxysj where jfzt='0' and qrbjtf='0' and to_char(jfrq,'yyyy-mm-dd') like '2011-05%' and cbdd='21' group by jndw,grjfbl,dwhrbl)
a,
(select jndw,grjfbl,dwhrbl,count(*) rs,nvl(sum(jfjs),0) jfjs,nvl(sum(grjn),0) grjn,nvl(sum(dwhr),0) dwhr from sdbx_ylbxysj where jfzt='4' and qrbjtf='0' and to_char(bjtfsj,'yyyy-mm-dd') like '2011-05%' and cbdd='21' group by jndw,grjfbl,dwhrbl)
b,
(select o.orgname orgname,y.jndw jndw from om_organization o,sdbx_ylbxysj y where y.jndw=o.orgid) c
where a.jndw=b.jndw and a.jndw=c.jndw and b.jndw=c.jndw
group by a.jndw,a.jfjs,a.grjn,a.dwhr,b.jndw,b.jfjs,b.grjn,b.dwhr, c.orgname,c.jndw,a.grjfbl,a.dwhrbl,b.grjfbl,b.dwhrbl,a.rs,b.rs出现的问题:
表a有数据,表b无数据,导致结果为空集我的目的:
不论表b有没有数据,只要表a有数据,就列出所有结果 表a+表b(可能有空值)补充:a.jndw=b.jndw(+)只适合于两张表,我这里用了三张表a、b、c所以也报错。
我的SQL:
select c.orgname,a.jndw ajndw,a.rs ars,a.grjfbl agrjfbl,a.dwhrbl adwhrbl,a.dwhr adwhr,a.jfjs ajfjs,a.grjn agrjn,b.jndw bjndw,b.rs brs,b.grjfbl bgrjfbl,b.dwhrbl bdwhrbl,b.jfjs bjfjs,b.grjn bgrjn,b.dwhr bdwhr
from
(select jndw,grjfbl,dwhrbl,count(*) rs,nvl(sum(jfjs),0) jfjs,nvl(sum(grjn),0) grjn,nvl(sum(dwhr),0) dwhr from sdbx_ylbxysj where jfzt='0' and qrbjtf='0' and to_char(jfrq,'yyyy-mm-dd') like '2011-05%' and cbdd='21' group by jndw,grjfbl,dwhrbl)
a,
(select jndw,grjfbl,dwhrbl,count(*) rs,nvl(sum(jfjs),0) jfjs,nvl(sum(grjn),0) grjn,nvl(sum(dwhr),0) dwhr from sdbx_ylbxysj where jfzt='4' and qrbjtf='0' and to_char(bjtfsj,'yyyy-mm-dd') like '2011-05%' and cbdd='21' group by jndw,grjfbl,dwhrbl)
b,
(select o.orgname orgname,y.jndw jndw from om_organization o,sdbx_ylbxysj y where y.jndw=o.orgid) c
where a.jndw=b.jndw and a.jndw=c.jndw and b.jndw=c.jndw
group by a.jndw,a.jfjs,a.grjn,a.dwhr,b.jndw,b.jfjs,b.grjn,b.dwhr, c.orgname,c.jndw,a.grjfbl,a.dwhrbl,b.grjfbl,b.dwhrbl,a.rs,b.rs出现的问题:
表a有数据,表b无数据,导致结果为空集我的目的:
不论表b有没有数据,只要表a有数据,就列出所有结果 表a+表b(可能有空值)补充:a.jndw=b.jndw(+)只适合于两张表,我这里用了三张表a、b、c所以也报错。
a.jndw ajndw,
a.rs ars,
a.grjfbl agrjfbl,
a.dwhrbl adwhrbl,
a.dwhr adwhr,
a.jfjs ajfjs,
a.grjn agrjn,
b.jndw bjndw,
b.rs brs,
b.grjfbl bgrjfbl,
b.dwhrbl bdwhrbl,
b.jfjs bjfjs,
b.grjn bgrjn,
b.dwhr bdwhr
from (select jndw,
grjfbl,
dwhrbl,
count(*) rs,
nvl(sum(jfjs), 0) jfjs,
nvl(sum(grjn), 0) grjn,
nvl(sum(dwhr), 0) dwhr,
max(t1.org_name) as org_name
from sdbx_ylbxysj t,(select o.orgname orgname,y.jndw jndw from om_organization o,sdbx_ylbxysj y where y.jndw=o.orgid) t1
where jfzt = '0'
and qrbjtf = '0'
and to_char(jfrq, 'yyyy-mm-dd') like '2011-05%'
and cbdd = '21'
and t.jndw=t1.jndw(+)
group by jndw, grjfbl, dwhrbl) a,
(select jndw,
grjfbl,
dwhrbl,
count(*) rs,
nvl(sum(jfjs), 0) jfjs,
nvl(sum(grjn), 0) grjn,
nvl(sum(dwhr), 0) dwhr
from sdbx_ylbxysj t,(select o.orgname orgname,y.jndw jndw from om_organization o,sdbx_ylbxysj y where y.jndw=o.orgid) t1
where jfzt = '4'
and qrbjtf = '0'
and to_char(bjtfsj, 'yyyy-mm-dd') like '2011-05%'
and cbdd = '21'
and t.jndw=t1.org_id
group by jndw, grjfbl, dwhrbl) b
where a.jndw = b.jndw(+)
group by a.jndw,
a.jfjs,
a.grjn,
a.dwhr,
b.jndw,
b.jfjs,
b.grjn,
b.dwhr,
a.orgname,
a.grjfbl,
a.dwhrbl,
b.grjfbl,
b.dwhrbl,
a.rs,
b.rs
SELECT C.ORGNAME,
A.JNDW AJNDW,
A.RS ARS,
A.GRJFBL AGRJFBL,
A.DWHRBL ADWHRBL,
A.DWHR ADWHR,
A.JFJS AJFJS,
A.GRJN AGRJN,
B.JNDW BJNDW,
B.RS BRS,
B.GRJFBL BGRJFBL,
B.DWHRBL BDWHRBL,
B.JFJS BJFJS,
B.GRJN BGRJN,
B.DWHR BDWHR
FROM (SELECT JNDW,
GRJFBL,
DWHRBL,
COUNT(*) RS,
NVL(SUM(JFJS), 0) JFJS,
NVL(SUM(GRJN), 0) GRJN,
NVL(SUM(DWHR), 0) DWHR
FROM SDBX_YLBXYSJ
WHERE JFZT = '0'
AND QRBJTF = '0'
AND TO_CHAR(JFRQ, 'yyyy-mm-dd') LIKE '2011-05%'
AND CBDD = '21'
GROUP BY JNDW, GRJFBL, DWHRBL) A
LEFT JOIN (SELECT JNDW,
GRJFBL,
DWHRBL,
COUNT(*) RS,
NVL(SUM(JFJS), 0) JFJS,
NVL(SUM(GRJN), 0) GRJN,
NVL(SUM(DWHR), 0) DWHR
FROM SDBX_YLBXYSJ
WHERE JFZT = '4'
AND QRBJTF = '0'
AND TO_CHAR(BJTFSJ, 'yyyy-mm-dd') LIKE '2011-05%'
AND CBDD = '21'
GROUP BY JNDW, GRJFBL, DWHRBL) B ON A.JNDW = B.JNDW
LEFT JOIN (SELECT O.ORGNAME ORGNAME, Y.JNDW JNDW
FROM OM_ORGANIZATION O, SDBX_YLBXYSJ Y
WHERE Y.JNDW = O.ORGID) C AND A.JNDW = C.JNDW AND B.JNDW = C.JNDW
GROUP BY A.JNDW,
A.JFJS,
A.GRJN,
A.DWHR,
B.JNDW,
B.JFJS,
B.GRJN,
B.DWHR,
C.ORGNAME,
C.JNDW,
A.GRJFBL,
A.DWHRBL,
B.GRJFBL,
B.DWHRBL,
A.RS,
B.RS
select * from a,b where a.a = b.a(+) and b.b >1 此处的b.b>1 要入到子查询里select * from a,(select * from b where b.b>1) b where a.a = b.a