【PL/SQL疑难杂症】关于PL/SQL语句问题,请求诊病???? 在PL/SQL中执行查询语句如下:
SELECT a.aab001 AS 单位编号
,b.aab004 AS 单位名称
,b.aab023 AS 主管部门
,nvl(MAX(a.aae002),0) AS 期号
--养老保险应缴信息
, (select sum(distinct(aab083)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老在职职工人数
, (select sum(ckb030) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老女职工人数
, (select sum(aab120) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴费基数总额
, (select sum(aab121) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老单位缴费基数总额
, (select sum(aab122)+sum(aab125)-sum(aab131) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴纳额
, (select (SUM(aab123)+sum(aab124))+(SUM(aab126)+sum(aab127))-(sum(aab132)+sum(aab133)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老单位缴纳额
, (select sum(aab204) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老滞纳金
, (select (sum(aab128)+sum(aab129)+sum(aab130))+sum(aab203) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老利息
, (select sum(aab137) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老金额小计
--失业保险应缴信息
, (select sum(aab083) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业在职职工人数
, (select sum(ckb030) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业女职工人数
, (select sum(aab120) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业个人缴费基数总额
, (select sum(aab121) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业单位缴费基数总额
, (select sum(aab122)+sum(aab125)-sum(aab131) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业个人缴纳额
, (select (sum(aab123)+sum(aab124))+(sum(aab126)+sum(aab127))-(sum(aab132)+sum(aab133)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业单位缴纳额
, (select sum(aab204) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业滞纳金
, (select sum(aab137) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业金额小计
--医疗应缴信息
, (select sum(aab083) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗在职职工人数
, (select sum(aab082) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 离退休职人员医疗补助人数
, (select sum(ckb007) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 公务员医疗补助人数
, (select sum(ckb030) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗女职工人数
, (select sum(aab120) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗个人缴费基数总额
, (select sum(aab121) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') AS 医疗单位缴费基数总额
, (select sum(aab122)+sum(aab125)-sum(aab131) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗个人缴纳额
, (select (sum(aab123)+sum(aab124))+(sum(aab126)+sum(aab127))-(sum(aab132)+sum(aab133)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗单位缴费划转金额
, (SELECT (sum(ckb006)+sum(ckb038))+sum(ckb112)-sum(ckb114) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 公务员医疗补助
, (select sum(zab002)+sum(zab003)-sum(zab010) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 大额
, (select sum(aab204) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗滞纳金
, (select sum(aab137) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗金额小计
--工伤应缴信息
, (select sum(aab083) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤在职职工人数
, (select sum(ckb030) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤女职工人数
, (select sum(aab120) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤个人缴费基数总额
, (select sum(aab121) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤单位缴费基数总额
, (select (sum(aab123)+sum(aab124))+(sum(aab126)+sum(aab127))-(sum(aab132)+sum(aab133)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤单位缴纳额
, (select sum(aab204) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤滞纳金
, (select sum(aab137) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤金额小计
,SUM(aab134) AS 本期应缴金额小计
,SUM(aab135) AS 补收金额小计
,SUM(aab136) AS 退收金额小计
,SUM(a.aab137) AS 应缴合计
,SUM(aab203) AS 利息
,SUM(aab204) AS 滞纳金
, (select max(aae036) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 经办时间
, (select aae011 from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') AS 经办人
FROM ab07 a, T_AB01 b
WHERE a.aab001 = b.aab001
and b.zab001 = '048200'
and b.aae119 = '1' --单位状态(1登记在册,2破产,3注销,4分立)
and a.aae140 in('1','2','3','4','5') --险种类型(1基本养老保险,2失业保险,3基本医疗保险,4工伤保险,5生育保险)
AND a.aae002 = 200901 --(aae002,--费款所属期;aae003,--对应费款所属期;aae036,--经办日期:aab191,--到帐日期)
AND b.aab001 not in('702332234','702333242','702360818','702360819','702360824','702360825') --(冗余单位)
group by a.aab001,b.aab004,b.aab023,a.aae002,a.aae036
order by a.aab001 ASC
问题是在PL/SQL中执行如上查询语句后,为什么有些查询结果行会有重复行(如下图黄色标志行)???如何能把这些重复行合算为一行?????曾经使用过CASE WHEN和DECODE等相关函数,都不能达到我的目的——行转列,故用SELECT检索,但会出现如上问题,请有经验的高手们指点!!!如何写这个SQL语句,能够把重复的行SUM合并为单行谢谢先。
,b.aab004 AS 单位名称
,b.aab023 AS 主管部门
,nvl(MAX(a.aae002),0) AS 期号
--养老保险应缴信息
, (select sum(distinct(aab083)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老在职职工人数
, (select sum(ckb030) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老女职工人数
, (select sum(aab120) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴费基数总额
........................
.........
FROM ab07 a, T_AB01 b
WHERE a.aab001 = b.aab001
and b.zab001 = '048200'
and b.aae119 = '1' --单位状态(1登记在册,2破产,3注销,4分立)
and a.aae140 in('1','2','3','4','5') --险种类型(1基本养老保险,2失业保险,3基本医疗保险,4工伤保险,5生育保险)
AND a.aae002 = 200901 --(aae002,--费款所属期;aae003,--对应费款所属期;aae036,--经办日期:aab191,--到帐日期)
AND b.aab001 not in('702332234','702333242','702360818','702360819','702360824','702360825') --(冗余单位)
group by a.aab001,b.aab004,b.aab023,a.aae002,a.aae036
order by a.aab001 ASC 改为:
SELECT a.aab001 AS 单位编号
,b.aab004 AS 单位名称
,b.aab023 AS 主管部门
,nvl(MAX(a.aae002),0) AS 期号
--养老保险应缴信息
,sum(distinct decode(aae140='1',aab083,0)) as as 养老在职职工人数
,sum(decode(aae140='1',ckb030,0)) as 养老女职工人数
,sum(decode(aae140='1',aab120,0)) as 养老个人缴费基数总额
........................
.........
FROM ab07 a, T_AB01 b
WHERE a.aab001 = b.aab001
and b.zab001 = '048200'
and b.aae119 = '1' --单位状态(1登记在册,2破产,3注销,4分立)
and a.aae140 in('1','2','3','4','5') --险种类型(1基本养老保险,2失业保险,3基本医疗保险,4工伤保险,5生育保险)
AND a.aae002 = 200901 --(aae002,--费款所属期;aae003,--对应费款所属期;aae036,--经办日期:aab191,--到帐日期)
AND b.aab001 not in('702332234','702333242','702360818','702360819','702360824','702360825') --(冗余单位)
group by a.aab001,b.aab004,b.aab023,a.aae002,a.aae036
order by a.aab001 ASC
SELECT a.aab001 AS 单位编号
,b.aab004 AS 单位名称
,b.aab023 AS 主管部门
,nvl(MAX(a.aae002),0) AS 期号
--养老保险应缴信息
,sum(distinct decode(aae140='1',aab083,0)) as as 养老在职职工人数
,sum(decode(aae140='1',ckb030,0)) as 养老女职工人数
,sum(decode(aae140='1',aab120,0)) as 养老个人缴费基数总额
........................
......... 这样子达不到如下换专列目的:
如下图:http://img.blog.163.com/photo/bI3hRKY_ohd29Jww13m5ew==/636696397320827694.jpg
为什么查询出来的结果:同一个单位会出现两条不同的记录行,而且各险种分别错行显示,但是有些单位就是一条查询记录行正确显示的??? 如何解决,让每个单位对应一条记录行输出,如上sql语句如何改造解决如上同一单位不错行正确进行一行显示的问题???
(正确应为:一个单位只显示一行输出结果行!!!)