我有三个SELECT 做的联合查询但是 在只有其中 两个SELECT有数据 一个SELECT没有数据的情况下我要如何让 没有数据的那个SELECT查出来的所有列都为0 如果我三个SELECT 正常查出来是这样1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8在有其中一条数据查出是空的情况下 要这样1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8这样我得在联合查询里如何处理?
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7 8在有其中一条数据查出是空的情况下 要这样1 2 3 4 5 6 7 8
0 0 0 0 0 0 0 0
1 2 3 4 5 6 7 8这样我得在联合查询里如何处理?
将每个查询语句更改为如下形式
select nvl(col1,0),a.* from(
select * from tt union all
select null,0,0,... from dual
order by 1)a
where col1 is not null or rownum=1
个人认为不是很有必要
select nvl(col1,0),a.* from(
select * from tt1 where... union all
select null,0,0,... from dual
order by 1)a
where col1 is not null or rownum=1
union all
select nvl(col1,0),a.* from(
select * from tt2 where... union all
select null,0,0,... from dual
order by 1)a
where col1 is not null or rownum=1
union all
select nvl(col1,0),a.* from(
select * from tt3 where... union all
select null,0,0,... from dual
order by 1)a
where col1 is not null or rownum=1
(
select 1 a,2 b from dual
union all
select 1,2 from dual
union all
select 1,2 from dual
union all
select 1,2 from dual
union all
select 1,2 from dual
union all
select 1,2 from dual
union all
select 1,2 from dual
union all
select 1,2 from dual
)
select a,b from
(
select a,b,rownum c from temp
union all
select 0,0,rownum from dual
connect by rownum<=8
order by c
)
--result:1 2
0 0
1 2
0 0
1 2
0 0
1 2
0 0
1 2
0 0
1 2
0 0
1 2
0 0
1 2
0 0--唯一不足的是,那个connect by 后面的条件,不能用COUNT(1) 的传值给它!