假设我查询统计质量缺陷的个数,sql语句是这样的:select nvl(count(od.errorcode),0)
from table1 a, table2 b
where a.ticketserial = b.ticketserial
AND a.mdate >= 20100411
AND a.mdate <= 20100420
and a.faccode in ('总一厂')
统计得的质量缺陷是0,好了问题来了,假如我加入了查询分厂的字段: select a.faccode,nvl(count(od.errorcode),0)
from table1 a, table2 b
where a.ticketserial = b.ticketserial
AND a.mdate >= 20100411
AND a.mdate <= 20100420
and a.faccode in ('总一厂')
group by a.faccode我想显示的是:a.faccode count(od.errorcode)
总一厂 0请问怎么做到?请高手指教
from table1 a, table2 b
where a.ticketserial = b.ticketserial
AND a.mdate >= 20100411
AND a.mdate <= 20100420
and a.faccode in ('总一厂')
统计得的质量缺陷是0,好了问题来了,假如我加入了查询分厂的字段: select a.faccode,nvl(count(od.errorcode),0)
from table1 a, table2 b
where a.ticketserial = b.ticketserial
AND a.mdate >= 20100411
AND a.mdate <= 20100420
and a.faccode in ('总一厂')
group by a.faccode我想显示的是:a.faccode count(od.errorcode)
总一厂 0请问怎么做到?请高手指教
select a.faccode,nvl(cnt,0)
from table1 a left join (select ticketserial , count(1) cnt from table2 group by ticketserial)
where a.ticketserial = b.ticketserial
AND a.mdate >= '20100411'
AND a.mdate <= '20100420'
AND a.faccode = '总一厂'
FROM table1 a, table2 b
WHERE a.ticketserial = b.ticketserial AND
a.mdate >= 20100411 AND
a.mdate <= 20100420 AND
a.faccode IN ('总一厂');
select a.faccode,nvl(cnt,0)
from table1 a left join (select ticketserial , count(1) cnt from table2 group by ticketserial) b
where a.ticketserial = b.ticketserial
AND a.mdate >= '20100411'
AND a.mdate <= '20100420'
AND a.faccode = '总一厂'
select a.faccode,nvl(count(od.errorcode),0)
from table1 a, table2 b
where a.ticketserial = b.ticketserial
AND a.mdate >='20100411'
AND a.mdate <='20100420'
and a.faccode='总一厂'
group by a.faccode