SQL语句列转行.

s63403048 2013-05-20 10:53:48
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
...全文
229 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
大明湖畔_帅锅 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
  • 打赏
  • 举报
 回复
引用 7 楼 sc273607742 的回复:
[quote=引用 4 楼 s63403048 的回复:] [quote=引用 1 楼 sc273607742 的回复:] group by 写错了 group by b.branchcode||'-'||b.branchname 试试
也不行.[/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
[/quote] 不行 这样直接在这里

then   distinct c.licenseno || substr(engineno, length(engineno) - 5, 6)  else null end)
缺失表达式了
哥眼神纯洁不 2013-05-20
  • 打赏
  • 举报
 回复
引用 4 楼 s63403048 的回复:
[quote=引用 1 楼 sc273607742 的回复:] group by 写错了 group by b.branchcode||'-'||b.branchname 试试
也不行.[/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
  • 打赏
  • 举报
 回复
引用 5 楼 HJ_daxian 的回复:
select的字段里面加上s.offreasons呢 用的时候不要这字段
也不行.
大明湖畔_帅锅 2013-05-20
  • 打赏
  • 举报
 回复
select的字段里面加上s.offreasons呢 用的时候不要这字段
s63403048 2013-05-20
  • 打赏
  • 举报
 回复
引用 1 楼 sc273607742 的回复:
group by 写错了 group by b.branchcode||'-'||b.branchname 试试
也不行.
s63403048 2013-05-20
  • 打赏
  • 举报
 回复
引用 2 楼 HJ_daxian 的回复:
group by 里面去掉s.offreasons试试
去掉s.offreasons, 不加机构显示也是错的
大明湖畔_帅锅 2013-05-20
  • 打赏
  • 举报
 回复
group by 里面去掉s.offreasons试试
哥眼神纯洁不 2013-05-20
  • 打赏
  • 举报
 回复
group by 写错了 group by b.branchcode||'-'||b.branchname 试试
s63403048 2013-05-20
  • 打赏
  • 举报
 回复
引用 9 楼 HJ_daxian 的回复:
里面写了一堆 就是要求个数量呀 这样可以么 

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
  • 打赏
  • 举报
 回复
引用 9 楼 HJ_daxian 的回复:
里面写了一堆 就是要求个数量呀 这样可以么 

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)
Sql语句实现表的行列转换，行转列，列转行
Sql语句实现表的行列转换，行转列，列转行
精典的SQL语句。行转列，列转行的语句
精典的SQL语句。行转列，列转行的语句精典的SQL语句。行转列，列转行的语句
Mysql 行转列，列转行 SQL语句和示例表结构SQL
Mysql 行转列，列转行 SQL语句和示例表结构SQL Mysql 行转列，列转行 SQL语句和示例表结构SQL
sql列转行以及行转列的通用存储过程
sql列转行以及行转列的通用存储过程！mssqlserver版本！
将SQL数据表的一列转换为一行.docx
将SQL数据库的一列多行 数据转换为 一行多列
Oracle

17,140

社区成员

55,259

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章