里面写了一堆 就是要求个数量呀 这样可以么 select b.branchcode||'-'||b.branchname as bran, c.licenseno || substr(engineno, length(engineno) - 5, 6) lic, sum(decode(s.offreasons,'保单价格原因',1,0)) offresions1, sum(decode(s.offreasons,'其它主体手续费较高',1,0)) offresions2, sum(decode(s.offreasons,'其它主体赠送礼品',1,0)) offresions3 from bascarinfo c, basmemberinfo m, offreasons s,sys_branch b where c.memberid = m.memberid and m.memberid = s.memberid and c.allotorgan = b.branchcode group by b.branchcode,b.branchname,licenseno,substr(engineno, length(engineno) - 5, 6)
感谢. 好了.这是最后版本的SQL.感谢神兽大哥.select b.branchcode||'-'||b.branchname as bran, sum(decode(s.offreasons,'保单价格原因',1,0)) offresions1, sum(decode(s.offreasons,'其它主体手续费较高',1,0)) offresions2, sum(decode(s.offreasons,'其它主体赠送礼品',1,0)) offresions3, sum(decode(s.offreasons,'理赔服务问题',1,0)) as offresions4 , sum(decode(s.offreasons,'过户、转卖他人',1,0)) as offresions5 , sum(decode(s.offreasons,'车辆报废',1,0)) as offresions6 , sum(decode(s.offreasons,'业务员离职或转其它支公司',1,0)) as offresions7 , sum(decode(s.offreasons,'本身为市场业务,流动性交强',1,0)) as offresions8 , sum(decode(s.offreasons,'其它主体关系客户',1,0)) as offresions9 , sum(decode(s.offreasons,'已在人保兄弟公司承保',1,0)) as offresions10 , sum(decode(s.offreasons,'已在其它主体承保',1,0)) as offresions11 , sum(decode(s.offreasons,'其它原因',1,0)) as offresions12 from bascarinfo c, basmemberinfo m, offreasons s,sys_branch b where c.memberid = m.memberid and m.memberid = s.memberid and c.allotorgan = b.branchcode group by b.branchcode,b.branchname,c.licenseno,substr(engineno, length(engineno) - 5, 6)
group by b.branchcode||'-'||b.branchname 试试
不加机构显示也是错的
b.branchcode||'-'||b.branchname as bran,
count(case s.offreasons when '保单价格原因' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions1 ,
count(case s.offreasons when '其它主体手续费较高' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions2,
count(case s.offreasons when '其它主体赠送礼品' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions3,
count(case s.offreasons when '理赔服务问题' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions4,
count(case s.offreasons when '过户、转卖他人' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions5,
count(case s.offreasons when '车辆报废' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions6,
count(case s.offreasons when '业务员离职或转其它支公司' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions7,
count(case s.offreasons when '本身为市场业务,流动性交强' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions8,
count(case s.offreasons when '其它主体关系客户' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions9,
count(case s.offreasons when '已在人保兄弟公司承保' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions10,
count(case s.offreasons when '已在其它主体承保' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions11,
count(case s.offreasons when '其它原因' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions12
from
bascarinfo c, basmemberinfo m, offreasons s,sys_branch b
where c.memberid = m.memberid
and m.memberid = s.memberid
and c.allotorgan = b.branchcode
group by b.branchcode||'-'||b.branchname
b.branchcode||'-'||b.branchname as bran,
count(case s.offreasons when '保单价格原因' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions1 ,
count(case s.offreasons when '其它主体手续费较高' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions2,
count(case s.offreasons when '其它主体赠送礼品' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions3,
count(case s.offreasons when '理赔服务问题' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions4,
count(case s.offreasons when '过户、转卖他人' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions5,
count(case s.offreasons when '车辆报废' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions6,
count(case s.offreasons when '业务员离职或转其它支公司' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions7,
count(case s.offreasons when '本身为市场业务,流动性交强' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions8,
count(case s.offreasons when '其它主体关系客户' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions9,
count(case s.offreasons when '已在人保兄弟公司承保' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions10,
count(case s.offreasons when '已在其它主体承保' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions11,
count(case s.offreasons when '其它原因' then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end) as offresions12
from
bascarinfo c, basmemberinfo m, offreasons s,sys_branch b
where c.memberid = m.memberid
and m.memberid = s.memberid
and c.allotorgan = b.branchcode
group by b.branchcode||'-'||b.branchname
不行
这样直接在这里
then distinct c.licenseno || substr(engineno, length(engineno) - 5, 6) else null end)缺失表达式了
select
b.branchcode||'-'||b.branchname as bran,
c.licenseno || substr(engineno, length(engineno) - 5, 6) lic,
sum(decode(s.offreasons,'保单价格原因',1,0)) offresions1,
sum(decode(s.offreasons,'其它主体手续费较高',1,0)) offresions2,
sum(decode(s.offreasons,'其它主体赠送礼品',1,0)) offresions3
from
bascarinfo c, basmemberinfo m, offreasons s,sys_branch b
where c.memberid = m.memberid
and m.memberid = s.memberid
and c.allotorgan = b.branchcode
group by b.branchcode,b.branchname,licenseno,substr(engineno, length(engineno) - 5, 6)
感谢.
好了.这是最后版本的SQL.感谢神兽大哥.select
b.branchcode||'-'||b.branchname as bran,
sum(decode(s.offreasons,'保单价格原因',1,0)) offresions1,
sum(decode(s.offreasons,'其它主体手续费较高',1,0)) offresions2,
sum(decode(s.offreasons,'其它主体赠送礼品',1,0)) offresions3,
sum(decode(s.offreasons,'理赔服务问题',1,0)) as offresions4 ,
sum(decode(s.offreasons,'过户、转卖他人',1,0)) as offresions5 ,
sum(decode(s.offreasons,'车辆报废',1,0)) as offresions6 ,
sum(decode(s.offreasons,'业务员离职或转其它支公司',1,0)) as offresions7 ,
sum(decode(s.offreasons,'本身为市场业务,流动性交强',1,0)) as offresions8 ,
sum(decode(s.offreasons,'其它主体关系客户',1,0)) as offresions9 ,
sum(decode(s.offreasons,'已在人保兄弟公司承保',1,0)) as offresions10 ,
sum(decode(s.offreasons,'已在其它主体承保',1,0)) as offresions11 ,
sum(decode(s.offreasons,'其它原因',1,0)) as offresions12
from
bascarinfo c, basmemberinfo m, offreasons s,sys_branch b
where c.memberid = m.memberid
and m.memberid = s.memberid
and c.allotorgan = b.branchcode
group by b.branchcode,b.branchname,c.licenseno,substr(engineno, length(engineno) - 5, 6)