SELECT
PRO_GROUPID,
PRO_GROUPDESC,
PROVINCECOMSNAME,
YEAR_VALUEPREM_ZT,
LY_VALUEPREM_ZT,
YEAR_VALUEPREM_PLAN_ZT,
CHANGE_RATE_ZT,
ACHIEVEMENT_RATE_ZT,
YEAR_VALUEPREM_YX,
LY_VALUEPREM_YX,
YEAR_VALUEPREM_PLAN_YX,
CHANGE_RATE_YX,
ACHIEVEMENT_RATE_YX,
YEAR_VALUEPREM_SZ,
LY_VALUEPREM_SZ,
YEAR_VALUEPREM_PLAN_SZ,
CHANGE_RATE_SZ,
ACHIEVEMENT_RATE_SZ,
YEAR_VALUEPREM_YDCF,
LY_VALUEPREM_YDCF,
YEAR_VALUEPREM_PLAN_YDCF,
CHANGE_RATE_YDCF,
ACHIEVEMENT_RATE_YDCF,
YEAR_VALUEPREM_YD,
LY_VALUEPREM_YD,
YEAR_VALUEPREM_PLAN_YD,
CHANGE_RATE_YD,
ACHIEVEMENT_RATE_YD,
YEAR_VALUEPREM_CF,
LY_VALUEPREM_CF,
YEAR_VALUEPREM_PLAN_CF,
CHANGE_RATE_CF,
ACHIEVEMENT_RATE_CF,
YEAR_VALUEPREM_GY,
LY_VALUEPREM_GY,
YEAR_VALUEPREM_PLAN_GY,
CHANGE_RATE_GY,
ACHIEVEMENT_RATE_GY,
LOADDATE,
SOURCE_TABLE
FROM (
SELECT
a.DATEID,
a.PRO_GROUPID,
pg.PRO_GROUPDESC,
pc.PROVINCECOMSNAME,
a.YEAR_VALUEPREM_ZT,
a.LY_VALUEPREM_ZT,
a.YEAR_VALUEPREM_PLAN_ZT,
1 AS ACHIEVEMENT_RATE_ZT,
1 AS CHANGE_RATE_ZT,
a.YEAR_VALUEPREM_YX,
a.LY_VALUEPREM_YX,
a.YEAR_VALUEPREM_PLAN_YX,
1 AS ACHIEVEMENT_RATE_YX,
1 AS CHANGE_RATE_YX,
a.YEAR_VALUEPREM_SZ,
a.LY_VALUEPREM_SZ,
a.YEAR_VALUEPREM_PLAN_SZ,
1 AS ACHIEVEMENT_RATE_SZ,
1 AS CHANGE_RATE_SZ,
a.YEAR_VALUEPREM_YDCF,
a.LY_VALUEPREM_YDCF,
a.YEAR_VALUEPREM_PLAN_YDCF,
1 AS ACHIEVEMENT_RATE_YDCF,
1 AS CHANGE_RATE_YDCF,
a.YEAR_VALUEPREM_YD,
a.LY_VALUEPREM_YD,
a.YEAR_VALUEPREM_PLAN_YD,
1 AS ACHIEVEMENT_RATE_YD,
1 AS CHANGE_RATE_YD,
a.YEAR_VALUEPREM_CF,
a.LY_VALUEPREM_CF,
a.YEAR_VALUEPREM_PLAN_CF,
1 AS ACHIEVEMENT_RATE_CF,
1 AS CHANGE_RATE_CF,
a.YEAR_VALUEPREM_GY,
a.LY_VALUEPREM_GY,
a.YEAR_VALUEPREM_PLAN_GY,
1 AS ACHIEVEMENT_RATE_GY,
1 AS CHANGE_RATE_GY,
a.LOADDATE,
'D006' AS SOURCE_TABLE
FROM
DMR_BD_A0101_D006 a
LEFT JOIN D_PRO_GROUP pg ON a.PRO_GROUPID = pg.PRO_GROUPID
LEFT JOIN D_PROVINCECOM pc ON a.PROVINCECOMCODE = pc.PROVINCECOMCODE
UNION ALL
SELECT
b.DATEID,
b.PRO_GROUPID,
pg.PRO_GROUPDESC,
pg.PRO_GROUPDESC || '合计' AS PROVINCECOMSNAME,
b.YEAR_VALUEPREM_ZT,
b.LY_VALUEPREM_ZT,
b.YEAR_VALUEPREM_PLAN_ZT,
1 AS ACHIEVEMENT_RATE_ZT,
1 AS CHANGE_RATE_ZT,
b.YEAR_VALUEPREM_YX,
b.LY_VALUEPREM_YX,
b.YEAR_VALUEPREM_PLAN_YX,
1 AS ACHIEVEMENT_RATE_YX,
1 AS CHANGE_RATE_YX,
b.YEAR_VALUEPREM_SZ,
b.LY_VALUEPREM_SZ,
b.YEAR_VALUEPREM_PLAN_SZ,
1 AS ACHIEVEMENT_RATE_SZ,
1 AS CHANGE_RATE_SZ,
b.YEAR_VALUEPREM_YDCF,
b.LY_VALUEPREM_YDCF,
b.YEAR_VALUEPREM_PLAN_YDCF,
1 AS ACHIEVEMENT_RATE_YDCF,
1 AS CHANGE_RATE_YDCF,
b.YEAR_VALUEPREM_YD,
b.LY_VALUEPREM_YD,
b.YEAR_VALUEPREM_PLAN_YD,
1 AS ACHIEVEMENT_RATE_YD,
1 AS CHANGE_RATE_YD,
b.YEAR_VALUEPREM_CF,
b.LY_VALUEPREM_CF,
b.YEAR_VALUEPREM_PLAN_CF,
1 AS ACHIEVEMENT_RATE_CF,
1 AS CHANGE_RATE_CF,
b.YEAR_VALUEPREM_GY,
b.LY_VALUEPREM_GY,
b.YEAR_VALUEPREM_PLAN_GY,
1 AS ACHIEVEMENT_RATE_GY,
1 AS CHANGE_RATE_GY,
b.LOADDATE,
'D006_1' AS SOURCE_TABLE
FROM
DMR_BD_A0101_D006_1 b
LEFT JOIN D_PRO_GROUP pg ON b.PRO_GROUPID = pg.PRO_GROUPID
UNION ALL
SELECT
c.DATEID,
0 AS PRO_GROUPID,
'系统合计' AS PRO_GROUPDESC,
'系统合计' AS PROVINCECOMSNAME,
c.YEAR_VALUEPREM_ZT,
c.LY_VALUEPREM_ZT,
c.YEAR_VALUEPREM_PLAN_ZT,
1 AS ACHIEVEMENT_RATE_ZT,
1 AS CHANGE_RATE_ZT,
c.YEAR_VALUEPREM_YX,
c.LY_VALUEPREM_YX,
c.YEAR_VALUEPREM_PLAN_YX,
1 AS ACHIEVEMENT_RATE_YX,
1 AS CHANGE_RATE_YX,
c.YEAR_VALUEPREM_SZ,
c.LY_VALUEPREM_SZ,
c.YEAR_VALUEPREM_PLAN_SZ,
1 AS ACHIEVEMENT_RATE_SZ,
1 AS CHANGE_RATE_SZ,
c.YEAR_VALUEPREM_YDCF,
c.LY_VALUEPREM_YDCF,
c.YEAR_VALUEPREM_PLAN_YDCF,
1 AS ACHIEVEMENT_RATE_YDCF,
1 AS CHANGE_RATE_YDCF,
c.YEAR_VALUEPREM_YD,
c.LY_VALUEPREM_YD,
c.YEAR_VALUEPREM_PLAN_YD,
1 AS ACHIEVEMENT_RATE_YD,
1 AS CHANGE_RATE_YD,
c.YEAR_VALUEPREM_CF,
c.LY_VALUEPREM_CF,
c.YEAR_VALUEPREM_PLAN_CF,
1 AS ACHIEVEMENT_RATE_CF,
1 AS CHANGE_RATE_CF,
c.YEAR_VALUEPREM_GY,
c.LY_VALUEPREM_GY,
c.YEAR_VALUEPREM_PLAN_GY,
1 AS ACHIEVEMENT_RATE_GY,
1 AS CHANGE_RATE_GY,
c.LOADDATE,
'D006_2' AS SOURCE_TABLE
FROM
DMR_BD_A0101_D006_2 c
) alls
ORDER BY
pro_groupID,
SOURCE_TABLE,
YEAR_VALUEPREM_ZT;这是我的sql YEAR_VALUEPREM_ZT字段排序未生效数据库中字段为NUMBER
最新发布