【PL/SQL疑难杂症】Select结果列转行为何出现同一单位对应两条记录行???PL/SQL执行查询语句如下:SELECT a.aab001 AS 单位编号
,b.aab004 AS 单位名称
,b.aab023 AS 主管部门
,nvl(MAX(a.aae002),0) AS 费款期号
--养老保险应缴信息
, (select sum(distinct(nvl(aab083,0))) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老在职职工人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老单位缴费基数总额
, (select sum(nvl(aab122,0)+nvl(aab125,0)-nvl(aab131,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴纳额
, (select SUM(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老单位缴纳额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老滞纳金
, (select sum(nvl(aab128,0)+nvl(aab129,0)+nvl(aab130,0)+nvl(aab203,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老利息
, (select sum(nvl(aab137,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老金额小计
--失业保险应缴信息
, (select sum(nvl(aab083,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业在职职工人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业单位缴费基数总额
, (select sum(nvl(aab122,0)+nvl(aab125,0)-nvl(aab131,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业个人缴纳额
, (select sum(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业单位缴纳额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业滞纳金
, (select sum(nvl(aab137,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业金额小计
--医疗应缴信息
, (select sum(nvl(aab083,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗在职职工人数
, (select sum(nvl(aab082,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 离退休职人员医疗补助人数
, (select sum(nvl(ckb007,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 公务员医疗补助人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') AS 医疗单位缴费基数总额
, (select sum(nvl(aab122,0)+nvl(aab125,0)-nvl(aab131,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗个人缴纳额
, (select sum(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗单位缴费划转金额
, (SELECT sum(nvl(ckb006,0)+nvl(ckb038,0)+nvl(ckb112,0)-nvl(ckb114,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 公务员医疗补助
, (select sum(nvl(zab002,0)+nvl(zab003,0)-nvl(zab010,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 大额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗滞纳金
, (select sum(nvl(aab137,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗金额小计
--工伤应缴信息
, (select sum(nvl(aab083,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤在职职工人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤单位缴费基数总额
, (select sum(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤单位缴纳额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤滞纳金
, (select sum(nvl(aab137,0)) 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 滞纳金
FROM ab07 a, T_AB01 b
WHERE a.aab001 = b.aab001
and b.zab001 = '0xxxx0'
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 = 200908 --期号(aae002,费款所属期;aae003,对应费款所属期;aae036, 经办日期;aab191, 到帐日期;aae034,审批日期)
group by a.aab001,b.aab004,b.aab023,a.aae002,a.aae036
order by a.aab001 ASC输入结果:AB07表结构:
,b.aab004 AS 单位名称
,b.aab023 AS 主管部门
,nvl(MAX(a.aae002),0) AS 费款期号
--养老保险应缴信息
, (select sum(distinct(nvl(aab083,0))) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老在职职工人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老单位缴费基数总额
, (select sum(nvl(aab122,0)+nvl(aab125,0)-nvl(aab131,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老个人缴纳额
, (select SUM(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老单位缴纳额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老滞纳金
, (select sum(nvl(aab128,0)+nvl(aab129,0)+nvl(aab130,0)+nvl(aab203,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老利息
, (select sum(nvl(aab137,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '1') as 养老金额小计
--失业保险应缴信息
, (select sum(nvl(aab083,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业在职职工人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业单位缴费基数总额
, (select sum(nvl(aab122,0)+nvl(aab125,0)-nvl(aab131,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业个人缴纳额
, (select sum(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业单位缴纳额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业滞纳金
, (select sum(nvl(aab137,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '2') as 失业金额小计
--医疗应缴信息
, (select sum(nvl(aab083,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗在职职工人数
, (select sum(nvl(aab082,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 离退休职人员医疗补助人数
, (select sum(nvl(ckb007,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 公务员医疗补助人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') AS 医疗单位缴费基数总额
, (select sum(nvl(aab122,0)+nvl(aab125,0)-nvl(aab131,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗个人缴纳额
, (select sum(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗单位缴费划转金额
, (SELECT sum(nvl(ckb006,0)+nvl(ckb038,0)+nvl(ckb112,0)-nvl(ckb114,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 公务员医疗补助
, (select sum(nvl(zab002,0)+nvl(zab003,0)-nvl(zab010,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 大额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗滞纳金
, (select sum(nvl(aab137,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '3') as 医疗金额小计
--工伤应缴信息
, (select sum(nvl(aab083,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤在职职工人数
, (select sum(nvl(ckb030,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤女职工人数
, (select sum(nvl(aab120,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤个人缴费基数总额
, (select sum(nvl(aab121,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤单位缴费基数总额
, (select sum(nvl(aab123,0)+nvl(aab124,0)+nvl(aab126,0)+nvl(aab127,0)-nvl(aab132,0)-nvl(aab133,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤单位缴纳额
, (select sum(nvl(aab204,0)) from AB07 where aab001 = a.aab001 and aae036 = a.aae036 and aae140 = '4') as 工伤滞纳金
, (select sum(nvl(aab137,0)) 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 滞纳金
FROM ab07 a, T_AB01 b
WHERE a.aab001 = b.aab001
and b.zab001 = '0xxxx0'
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 = 200908 --期号(aae002,费款所属期;aae003,对应费款所属期;aae036, 经办日期;aab191, 到帐日期;aae034,审批日期)
group by a.aab001,b.aab004,b.aab023,a.aae002,a.aae036
order by a.aab001 ASC输入结果:AB07表结构:
(正确应为:一个单位只显示一行输出结果行!!!)如何让查询结果正确显示:一个单位只显示一行记录输出结果行!!!
分组字段的值有不一样的,导致分成两条,我刚看了下SQL,应该是a.aae036 这个分组字段的问题,你把这个去掉看看
再次谢谢谢谢提醒的老师们!!!呵呵