select e.enterprisenum,count(f.pagenum) pages,count(distinct(f.docno)) doc,count(f.datatypeid in (select * from ic_imageofroll u where u.datatypeid='57'))
from ic_roll_define e ,ic_imageofroll f
where e.enterprisenum='gf-2002-510100-01-p-1518' and e.rollid=f.rollid
group by e.enterprisenum提示:ORA:00907--缺少右括号!请各位大侠帮忙指点一下~~~可能SLQ语句有问题~~
datatypeid满足等于‘57’的总数,如果没有就为‘0’~~~谢谢各位~~~
from ic_roll_define e ,ic_imageofroll f
where e.enterprisenum='gf-2002-510100-01-p-1518' and e.rollid=f.rollid
group by e.enterprisenum提示:ORA:00907--缺少右括号!请各位大侠帮忙指点一下~~~可能SLQ语句有问题~~
datatypeid满足等于‘57’的总数,如果没有就为‘0’~~~谢谢各位~~~
enterprisenum pages doc datatypeid
gf-2002-510100-01-p-1518 257 49 0
放到WHERE后面去select
e.enterprisenum,count(f.pagenum) pages,
count(distinct(f.docno)) doc,
count(f.datatypeid)
from ic_roll_define e ,ic_imageofroll f
where e.enterprisenum='gf-2002-510100-01-p-1518' and e.rollid=f.rollid
and f.datatypeid in (select * from ic_imageofroll u where u.datatypeid='57'))
group by e.enterprisenum
试试
nvl(count(f.datatypeid),0)
from ic_roll_define e ,ic_imageofroll f
where e.enterprisenum='gf-2002-510100-01-p-1518' and e.rollid=f.rollid
and f.datatypeid in (select datatypeid from ic_imageofroll u where u.datatypeid='57')
group by e.enterprisenum
from ic_roll_define e ,ic_imageofroll f
where e.enterprisenum='gf-2002-510100-01-p-1518' and e.rollid=f.rollid
group by e.enterprisenum
count(f.pagenum) pages,
count(distinct(f.docno)) doc,
null
from ic_roll_define e, ic_imageofroll f
where e.enterprisenum = 'gf-2002-510100-01-p-1518'
and e.rollid = f.rollid
group by e.enterprisenum
union all
select null, null, null, count(f.datatypeid)
from ic_roll_define e, ic_imageofroll f
where e.enterprisenum = 'gf-2002-510100-01-p-1518'
and e.rollid = f.rollid
and datatypeid = '57'
group by e.enterprisenum
可以用UNION来代替你想要的效果,因为最后的一个COUNT是不能那样写的,要用一条SQL来实现,可以用UNION.
不一定是你完全想要的SQL,可能你要根据你自己的需求修改一下...