生成的临时表结构--本质还是对这个临时表的理解个人感觉

查询语句：

SELECT DIM_27_CODE AS DIM_27_CODE,
MAX(DIM_27_DISPLAY) AS DIM_27_DISPLAY,
DIM_29_CODE AS DIM_29_CODE,
MAX(DIM_29_DISPLAY) AS DIM_29_DISPLAY,
DIM_DATE_CODE AS DIM_DATE_LAST_CODE,
SUM(DIM_95_DATA) AS DIM_95_DATA
FROM vhbiyf.rep_temp_report_47
WHERE DIM_DATE_CODE IN (‘201409‘)
AND DIM_DATE_LEVEL = ‘YEAR_MONTH‘
AND DIM_21_CODE IN (‘1000‘)
AND DIM_27_CODE IN (‘1‘, ‘2‘, ‘3‘, ‘1‘, ‘2‘, ‘3‘)
GROUP BY
DIM_27_CODE,
DIM_29_CODE,
DIM_DATE_CODE
ORDER BY
DIM_27_CODE ASC,
DIM_29_CODE ASC

页面请求：

创建临时表的SQL：

CREATE TABLE temp_DIM_95
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_27
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_29
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_21
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_21_comp_name VARCHAR(4000),
DIM_21_comp_code VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
INSERT INTO temp_DIM_95
SELECT DIM_95.fint_year AS DIM_95_fint_year,
DIM_95.fint_month AS DIM_95_fint_month,
DIM_95.a_id AS DIM_95_a_id,
DIM_95.p_id AS DIM_95_p_id,
DIM_95.comp_code AS DIM_95_comp_code,
SUM(DIM_95.num) AS DIM_95_num
FROM (
SELECT YYB_ORDER.*
FROM YYB_ORDER YYB_ORDER
) DIM_95
WHERE DIM_95.fint_year = ‘2014‘
AND DIM_95.fint_month = ‘09‘
GROUP BY
DIM_95.fint_year,
DIM_95.fint_month,
DIM_95.a_id,
DIM_95.p_id,
DIM_95.comp_code;
INSERT INTO temp_DIM_27
SELECT DIM_95.DIM_95_fint_year AS DIM_95_fint_year,
DIM_95.DIM_95_fint_month AS DIM_95_fint_month,
DIM_95.DIM_95_a_id AS DIM_95_a_id,
DIM_95.DIM_95_p_id AS DIM_95_p_id,
DIM_95.DIM_95_comp_code AS DIM_95_comp_code,
DIM_27.name AS DIM_27_name,
DIM_27.id AS DIM_27_id,
SUM(DIM_95.DIM_95_num) AS DIM_95_num
FROM temp_DIM_95 DIM_95
INNER JOIN (
SELECT YYB_AREA.*
FROM YYB_AREA YYB_AREA
) DIM_27
ON (1 = 1 AND DIM_27.id = DIM_95.DIM_95_a_id)
GROUP BY
DIM_95.DIM_95_fint_year,
DIM_95.DIM_95_fint_month,
DIM_95.DIM_95_a_id,
DIM_95.DIM_95_p_id,
DIM_95.DIM_95_comp_code,
DIM_27.name,
DIM_27.id;
INSERT INTO temp_DIM_29
SELECT DIM_27.DIM_95_fint_year AS DIM_95_fint_year,
DIM_27.DIM_95_fint_month AS DIM_95_fint_month,
DIM_27.DIM_95_a_id AS DIM_95_a_id,
DIM_27.DIM_95_p_id AS DIM_95_p_id,
DIM_27.DIM_95_comp_code AS DIM_95_comp_code,
DIM_27.DIM_27_name AS DIM_27_name,
DIM_27.DIM_27_id AS DIM_27_id,
DIM_29.name AS DIM_29_name,
DIM_29.id AS DIM_29_id,
SUM(DIM_27.DIM_95_num) AS DIM_95_num
FROM temp_DIM_27 DIM_27
INNER JOIN (
SELECT YYB_PRO.*
FROM YYB_PRO YYB_PRO
) DIM_29
ON (1 = 1 AND DIM_29.id = DIM_27.DIM_95_p_id)
GROUP BY
DIM_27.DIM_95_fint_year,
DIM_27.DIM_95_fint_month,
DIM_27.DIM_95_a_id,
DIM_27.DIM_95_p_id,
DIM_27.DIM_95_comp_code,
DIM_27.DIM_27_name,
DIM_27.DIM_27_id,
DIM_29.name,
DIM_29.id;
INSERT INTO temp_DIM_21
SELECT DIM_29.DIM_95_fint_year AS DIM_95_fint_year,
DIM_29.DIM_95_fint_month AS DIM_95_fint_month,
DIM_29.DIM_95_a_id AS DIM_95_a_id,
DIM_29.DIM_95_p_id AS DIM_95_p_id,
DIM_29.DIM_95_comp_code AS DIM_95_comp_code,
DIM_29.DIM_27_name AS DIM_27_name,
DIM_29.DIM_27_id AS DIM_27_id,
DIM_29.DIM_29_name AS DIM_29_name,
DIM_29.DIM_29_id AS DIM_29_id,
DIM_21.comp_name AS DIM_21_comp_name,
DIM_21.comp_code AS DIM_21_comp_code,
SUM(DIM_29.DIM_95_num) AS DIM_95_num
FROM temp_DIM_29 DIM_29
INNER JOIN (
SELECT SYS_COMPANY.*
FROM SYS_COMPANY SYS_COMPANY
) DIM_21
ON (1 = 1 AND DIM_21.comp_code = DIM_29.DIM_95_comp_code)
WHERE DIM_21.comp_code IN (‘1000‘)
GROUP BY
DIM_29.DIM_95_fint_year,
DIM_29.DIM_95_fint_month,
DIM_29.DIM_95_a_id,
DIM_29.DIM_95_p_id,
DIM_29.DIM_95_comp_code,
DIM_29.DIM_27_name,
DIM_29.DIM_27_id,
DIM_29.DIM_29_name,
DIM_29.DIM_29_id,
DIM_21.comp_name,
DIM_21.comp_code;
SELECT DIM_21.DIM_27_id AS DIM_27_CODE,
MAX(DIM_21.DIM_27_name) AS DIM_27_DISPLAY,
DIM_21.DIM_29_id AS DIM_29_CODE,
MAX(DIM_21.DIM_29_name) AS DIM_29_DISPLAY,
‘201409‘ AS DIM_DATE_CODE,
‘YEAR_MONTH‘ AS DIM_DATE_LEVEL,
DIM_21.DIM_21_comp_code AS DIM_21_CODE,
MAX(DIM_21.DIM_21_comp_name) AS DIM_21_DISPLAY,
SUM(DIM_21.DIM_95_num) AS DIM_95_DATA
FROM temp_DIM_21 DIM_21
GROUP BY
DIM_21.DIM_27_id,
DIM_21.DIM_29_id,
DIM_21.DIM_21_comp_code
ORDER BY
DIM_21.DIM_27_id ASC,
DIM_21.DIM_29_id ASC,
DIM_21.DIM_21_comp_code ASC;DROP TABLE temp_DIM_95;DROP TABLE temp_DIM_27;
DROP TABLE temp_DIM_29;DROP TABLE temp_DIM_21;

页面设计要求：

表格联动

图标联动

图表详细

指标自定义公式

BI设计