一个SQL连接的问题

[yaotomo]

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
order by u.c_measuresiteid

查询结果是下图所示


TB_MEASURESITEQCINFO 表里有所有的c_measuresiteid，c_extendfieldd的值有4个（1,3,5,7）
我想得到的效果是将没有的统计值都显示为0，如下所示

001 1 88 1676.86
001 3 80 2474.76
001 5 0 0 --计量站点001业务类型编码为5，没有统计信息，则显示为0
001 7 11 190.56
002 1 0 0 --计量站点002业务类型编码为1，没有统计信息，则显示为0
002 3 75 1624.26
002 5 0 0 --计量站点002业务类型编码为5，没有统计信息，则显示为0
002 7 0 0 --计量站点002业务类型编码为7，没有统计信息，则显示为0

请各位大神指点，能通过连接查询实现吗

[CT_LXL]

引用 楼主 yaotomo 的回复:

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
order by u.c_measuresiteid

查询结果是下图所示 TB_MEASURESITEQCINFO 表里有所有的c_measuresiteid，c_extendfieldd的值有4个（1,3,5,7） 我想得到的效果是将没有的统计值都显示为0，如下所示 001 1 88 1676.86 001 3 80 2474.76 001 5 0 0 --计量站点001业务类型编码为5，没有统计信息，则显示为0 001 7 11 190.56 002 1 0 0 --计量站点002业务类型编码为1，没有统计信息，则显示为0 002 3 75 1624.26 002 5 0 0 --计量站点002业务类型编码为5，没有统计信息，则显示为0 002 7 0 0 --计量站点002业务类型编码为7，没有统计信息，则显示为0 请各位大神指点，能通过连接查询实现吗

写了个例子供你参考

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;

[yaotomo]

引用 1 楼 zlloct 的回复:

写了个例子供你参考

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;

用下面的代码执行，还是原来的效果。。。

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 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

[CT_LXL]

引用 2 楼 yaotomo 的回复:

select t5.c_measuresiteid, t5.c1, nvl(t4.n_count, 0), nvl(t4.n_weight, 0)
  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

[yaotomo]

引用 3 楼 zlloct 的回复:

from dual) t2) t5 where t4.c_measuresiteid(+) = t5.c_measuresiteid and t4.c_extendfieldd(+) = t5.c1 [/code]

太感谢您了！！！！