FULL JOIN 的写法就不写了,写一种其他的实现方式 SELECT _DATE,_PEOPLE, MAX(_VALUE_A) AS _VALUE_A, MAX(_VALUE_B) AS _VALUE_B, MAX(_VALUE_C) AS _VALUE_C FROM ( SELECT _DATE,_PEOPLE,_VALUE_A,NULL AS _VALUE_B,NULL AS _VALUE_C FROM A UNION ALL SELECT _DATE,_PEOPLE,NULL AS _VALUE_A,_VALUE_B,NULL AS _VALUE_C FROM B UNION ALL SELECT _DATE,_PEOPLE,NULL AS _VALUE_A,NULL AS _VALUE_B,_VALUE_C FROM C ) GROUP BY _DATE , _PEOPLE
select _DATE, _PEOPLE , _VALUE_A, _VALUE_B, _VALUE_C from 这一段不会写 where _DATE between '2010' and '2014' and _PEOPLE in ('张三', '李四')
这种情况可以用,但是我的列很多,表也很多,还有简单的方法么,想看学下join的该如何用
SELECT NVL(T._DATE,C._DATE) _DATE,NVL(T._PEOPLE,C._PEOPLE) _PEOPLE,T._VALUE_A,T._VALUE_B,C._VALUE_C FROM (SELECT NVL(A._DATE,B._DATE) _DATE,NVL(A._PEOPLE,B._PEOPLE) _PEOPLE,A._VALUE_A,B._VALUE_B FROM A FULL JOIN B ON A._DATE=B._DATE AND A._PEOPLE=B._PEOPLE) T FULL JOIN C ON T._DATE=C._DATE AND T._PEOPLE=C._PEOPLE
我还有个地方想请教下你们, 能不能在join之前先加过滤条件, .....from A full join B on A.date = B.date 像这样=====>...from A where A.date > '2014' join B on A.date = B.date and B.date > '2014'
以下三个例子可以供参考:(直接运行看结果) with a as (select 2011 dt from dual union all select 2012 dt from dual union all select 2015 dt from dual), b as (select 2011 dt from dual union all select 2013 dt from dual union all select 2015 dt from dual) select * from a full join b on a.dt = b.dt;
with a as (select 2011 dt from dual union all select 2012 dt from dual union all select 2015 dt from dual), b as (select 2011 dt from dual union all select 2013 dt from dual union all select 2015 dt from dual) select * from a full join b on a.dt = b.dt and a.dt>2014 and b.dt>2014;
with a as (select 2011 dt from dual union all select 2012 dt from dual union all select 2015 dt from dual), b as (select 2011 dt from dual union all select 2013 dt from dual union all select 2015 dt from dual) select * from (select * from a where a.dt > 2014) t1 full join (select * from b where b.dt > 2014) t2 on t1.dt = t2.dt;
SELECT _DATE,_PEOPLE, MAX(_VALUE_A) AS _VALUE_A, MAX(_VALUE_B) AS _VALUE_B, MAX(_VALUE_C) AS _VALUE_C
FROM
(
SELECT _DATE,_PEOPLE,_VALUE_A,NULL AS _VALUE_B,NULL AS _VALUE_C FROM A
UNION ALL
SELECT _DATE,_PEOPLE,NULL AS _VALUE_A,_VALUE_B,NULL AS _VALUE_C FROM B
UNION ALL
SELECT _DATE,_PEOPLE,NULL AS _VALUE_A,NULL AS _VALUE_B,_VALUE_C FROM C
)
GROUP BY _DATE , _PEOPLE
我的三个表_DATE , _PEOPLE字段值不一定都是一样的,怎么处理。full join 的on中的条件是啥呢,A
_DATE: 2014
_PEOPLE: 张三
_VALUE_A: 800B
_DATE: 2013
_PEOPLE: 张三
_VALUE_A: 900C
_DATE: 2014
_PEOPLE: 李四
_VALUE_A: 700这样的数据fulljoin怎么处理呢,谢谢
select _DATE, _PEOPLE , _VALUE_A, _VALUE_B, _VALUE_C
from
这一段不会写
where
_DATE between '2010' and '2014'
and _PEOPLE in ('张三', '李四')
这种情况可以用,但是我的列很多,表也很多,还有简单的方法么,想看学下join的该如何用
FROM
(SELECT NVL(A._DATE,B._DATE) _DATE,NVL(A._PEOPLE,B._PEOPLE) _PEOPLE,A._VALUE_A,B._VALUE_B
FROM A FULL JOIN B
ON A._DATE=B._DATE AND A._PEOPLE=B._PEOPLE) T
FULL JOIN C
ON T._DATE=C._DATE AND T._PEOPLE=C._PEOPLE
sql语句执行的时候是先根据on条件进行连接,然后再根据where进行筛选如果是内连接的话,写在哪都无所谓
如果是外连接或是全连接的话,写在两个位置的查询结果是有差别的
能不能在join之前先加过滤条件,
.....from A full join B on A.date = B.date
像这样=====>...from A where A.date > '2014' join B on A.date = B.date and B.date > '2014'
以下三个例子可以供参考:(直接运行看结果) with a as
(select 2011 dt
from dual
union all
select 2012 dt
from dual
union all
select 2015 dt
from dual),
b as
(select 2011 dt
from dual
union all
select 2013 dt
from dual
union all
select 2015 dt
from dual)
select * from a full join b on a.dt = b.dt;
with a as
(select 2011 dt
from dual
union all
select 2012 dt
from dual
union all
select 2015 dt
from dual),
b as
(select 2011 dt
from dual
union all
select 2013 dt
from dual
union all
select 2015 dt
from dual)
select * from a full join b on a.dt = b.dt and a.dt>2014 and b.dt>2014;
with a as
(select 2011 dt
from dual
union all
select 2012 dt
from dual
union all
select 2015 dt
from dual),
b as
(select 2011 dt
from dual
union all
select 2013 dt
from dual
union all
select 2015 dt
from dual)
select *
from (select * from a where a.dt > 2014) t1
full join (select * from b where b.dt > 2014) t2
on t1.dt = t2.dt;