1.6w+
社区成员
SQL语句列转行.
RT.列转行成功.
但是带入机构条件就提示不是单组分组函数.
去掉
这一段,就可以显示了
(需求里必须显示机构咧.)
麻烦了
但是带入机构条件就提示不是单组分组函数.
去掉
b.branchcode||'-'||b.branchname as bran,
这一段,就可以显示了
(需求里必须显示机构咧.)
麻烦了
--按脱保原因调查表的统计方法
select
b.branchcode||'-'||b.branchname as bran,
sum(case s.offreasons when '保单价格原因' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions1 ,
sum(case s.offreasons when '其它主体手续费较高' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions2,
sum(case s.offreasons when '其它主体赠送礼品' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions3,
sum(case s.offreasons when '理赔服务问题' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions4,
sum(case s.offreasons when '过户、转卖他人' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions5,
sum(case s.offreasons when '车辆报废' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions6,
sum(case s.offreasons when '业务员离职或转其它支公司' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions7,
sum(case s.offreasons when '本身为市场业务，流动性交强' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions8,
sum(case s.offreasons when '其它主体关系客户' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions9,
sum(case s.offreasons when '已在人保兄弟公司承保' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions10,
sum(case s.offreasons when '已在其它主体承保' then count(distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)) else null end) as offresions11,
sum(case s.offreasons when '其它原因' then count(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,s.offreasons
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
大明湖畔_帅锅 2013-05-20
里面写了一堆 就是要求个数量呀 这样可以么
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)
s63403048 2013-05-20
也不行.[/quote]
感觉你的sum有点多余，用下面的试试
select
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)
哥眼神纯洁不 2013-05-20
也不行.[/quote]
感觉你的sum有点多余，用下面的试试
select
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
s63403048 2013-05-20
也不行.
大明湖畔_帅锅 2013-05-20
select的字段里面加上s.offreasons呢 用的时候不要这字段
s63403048 2013-05-20
也不行.
s63403048 2013-05-20
去掉s.offreasons,
不加机构显示也是错的
大明湖畔_帅锅 2013-05-20
group by 里面去掉s.offreasons试试
哥眼神纯洁不 2013-05-20
group by 写错了
group by b.branchcode||'-'||b.branchname 试试
s63403048 2013-05-20
这个到底是为什么呢.!
s63403048 2013-05-20
感谢.
好了.这是最后版本的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)
基于微服务框架的旅游门户的设计与实现.rar 内容包括详细设计文档word版，附带开题报告和相关PPT等文档，供大家参考学习。摘要：本系统是基于微服务的，分为后PC端旅游后台管理和PC端旅游门户两部分，其中，旅游门户实现了对景点、酒店、旅游攻略的查看，能够完成游客对旅游团、酒店、景点等消费的预定，公告模块为游客和旅行社之间建立了沟通的桥梁；管理人员登录旅游门户的后台管理系统，对系统进行维护，保证网站能够安全稳定的运行，同时，人们不用花费大量的时间和精神来管理旅游的方方面面，旅游网站可以减少管理人员的精力和时间，能够使管理人员有更多的时间去服务游客，提高了游客的体验。
旅游网站的设计可以促进济宁旅游业的发展，为游客选择旅游目的地，节省
游客和管理人员的时间，提高游客旅游的积极性，让旅游变得异常轻松。
