写了个例子供你参考with t as
(select 1 id, 1 c1, 100 c2
from dual
union all
select 1 id, 3 c1, 100 c2
from dual
union all
select 1 id, 5 c1, 100 c2
from dual
union all
select 2 id, 7 c1, 100 c2
from dual
union all
select 3 id, 5 c1, 100 c2
from dual),
t1 as
(select 1 c1
from dual
union all
select 3 c1
from dual
union all
select 5 c1
from dual
union all
select 7 c1
from dual)
select t2.id, t2.c1, nvl(t.c2, 0)
from t, (select id, t1.c1 from (select distinct id from t), t1) t2
where t.id(+) = t2.id
and t.c1(+) = t2.c1
order by t2.id, t2.c1;
解决方案 »
- 请问如何随机取出值并插入?
- 这个语句怎么优化成join形式的呀?
- Win7+Oracle8.1.7客户端+PL/SQL7.0 中文竖排问题如何解决?
- 急救!!!!!!!!!多谢大家了!!!!!!!!!!!!!!!!!在线等
- 关于查询的问题
- 请问ORA-00472 PMON process terminated with error的 错误怎么解决?
- 存储过程怪事
- 如何用SQL*PLUS查出表中那些重复的记录???
- 请教: 我的oracle9i装完了, 可是系统盘大了5个G, 已经没有空间用了. 不解!!
- 我的版本为Oracle8.1.7,在PL-SQL中写存储过程出问题?
- 有句SQL语句看不懂求帮忙解释下
- 关于关联两个表字符串拼接的一个问题
(select 1 c1
from dual
union all
select 3 c1
from dual
union all
select 5 c1
from dual
union all
select 7 c1
from dual)
select t2.c_measuresiteid,t2.c1,nvl(count(t.c_measuredocid),0)
from TMSR_MEASUREDOCINFO t, (select c_measuresiteid,t1.c1 from TB_MEASURESITEQCINFO,t1) t2
where t.c_winsvsdatamutual(+) = t2.c_measuresiteid
and t.c_extendfieldd(+) = t2.c1
and t.c_carrytoolstypeid = 'QC'
and t.i_msrfinishflag = '1'
and t.c_measurebegindate >= '2014-07-27 00:00:00'
and t.c_measurebegindate <= '2014-07-28 00:00:00'
group by t2.c_measuresiteid,t2.c1
如果单独执行下面语句可以列出所有计量站点和业务类型编码的组合,但是再和计量表联合查询就不行了with t1 as
(select 1 c1
from dual
union all
select 3 c1
from dual
union all
select 5 c1
from dual
union all
select 7 c1
from dual)
select c_measuresiteid,t1.c1 from TB_MEASURESITEQCINFO,t1
from (select u.c_measuresiteid,
t.c_extendfieldd,
count(t.c_measuredocid) n_count,
sum(t.n_suttleweight) / 1000 n_weight
from TMSR_MEASUREDOCINFO t
left join TB_MEASURESITEQCINFO u
on t.c_winsvsdatamutual = u.c_measuresiteid
where t.c_carrytoolstypeid = 'QC'
and t.i_msrfinishflag = '1'
and t.c_winsvsdatamutual is not null
and t.c_measurebegindate >= '2014-07-27 00:00:00'
and t.c_measurebegindate <= '2014-07-28 00:00:00'
group by u.c_measuresiteid, t.c_extendfieldd) t4,
(select c_measuresiteid, t2.c1
from (select distinct u.c_measuresiteid
from TMSR_MEASUREDOCINFO t
left join TB_MEASURESITEQCINFO u
on t.c_winsvsdatamutual = u.c_measuresiteid
where t.c_carrytoolstypeid = 'QC'
and t.i_msrfinishflag = '1'
and t.c_winsvsdatamutual is not null
and t.c_measurebegindate >= '2014-07-27 00:00:00'
and t.c_measurebegindate <= '2014-07-28 00:00:00') t1,
(select 1 c1
from dual
union all
select 3 c1
from dual
union all
select 5 c1
from dual
union all
select 7 c1
from dual) t2) t5
where t4.c_measuresiteid(+) = t5.c_measuresiteid
and t4.c_extendfieldd(+) = t5.c1