求一存储过程,能根据不同的报表名称(物理表名),实现对需要的字段进行汇总。
CREATE TABLE T_DB_ORGA_CONFIG
(
ORGANIZATION_CODE VARCHAR(100) /*机构代码*/,
ORGANIZATION_NAME VARCHAR(200) /*机构名称*/,
PARENT_ORG_CODE VARCHAR(100) /*父级机构代码*/
)
CREATE TABLE T_LIABILITY --资产负债表
(
ITEM_ID VARCHAR(50)/*项目ID*/,
YEAR_MONTH VARCHAR(6) /*年月*/,
START_QTY INT /*年初数*/,
END_QTY INT/*期末数*/,
NET_CODE VARCHAR(10)/*网点编码*/
)
CREATE TABLE T_CASH_FLOW --现金流量表
(
ITEM_ID VARCHAR(50)/*项目ID*/,
YEAR_MONTH VARCHAR(6) /*年月*/,
MONTH_COUNT INT/*本月数*/,
LAST_YEAR_TOTAL INT /*上年同期累计*/,
PERIOD_TOTAL INT/*本期累计*/,
NET_CODE VARCHAR(10)/*网点编码*/
)
INSERT INTO T_DB_ORGA_CONFIG SELECT 'D0','广东省',''
INSERT INTO T_DB_ORGA_CONFIG SELECT 'D001','广州市','D0'
INSERT INTO T_DB_ORGA_CONFIG SELECT 'D002','深圳市','D0'
INSERT INTO T_LIABILITY SELECT 'A001_0001','201206',1,3,'D001'
INSERT INTO T_LIABILITY SELECT 'A001_0002','201206',4,9,'D001'
INSERT INTO T_LIABILITY SELECT 'A001_0003','201206',1,5,'D001'
INSERT INTO T_LIABILITY SELECT 'A001_0004','201206',6,4,'D001'
INSERT INTO T_LIABILITY SELECT 'A001_0001','201206',2,2,'D002'
INSERT INTO T_LIABILITY SELECT 'A001_0002','201206',4,3,'D002'
INSERT INTO T_LIABILITY SELECT 'A001_0003','201206',7,8,'D002'
INSERT INTO T_LIABILITY SELECT 'A001_0004','201206',6,3,'D002'
INSERT INTO T_CASH_FLOW SELECT 'A002_0001','201206',1,8,2,'D001'
INSERT INTO T_CASH_FLOW SELECT 'A002_0002','201206',5,1,4,'D001'
INSERT INTO T_CASH_FLOW SELECT 'A002_0003','201206',3,7,3,'D001'
INSERT INTO T_CASH_FLOW SELECT 'A002_0004','201206',2,8,5,'D001'
INSERT INTO T_CASH_FLOW SELECT 'A002_0005','201206',7,4,7,'D001'
INSERT INTO T_CASH_FLOW SELECT 'A002_0006','201206',9,5,6,'D001'
INSERT INTO T_CASH_FLOW SELECT 'A002_0001','201206',6,7,0,'D002'
INSERT INTO T_CASH_FLOW SELECT 'A002_0002','201206',4,2,4,'D002'
INSERT INTO T_CASH_FLOW SELECT 'A002_0003','201206',8,0,6,'D002'
INSERT INTO T_CASH_FLOW SELECT 'A002_0004','201206',2,4,4,'D002'
INSERT INTO T_CASH_FLOW SELECT 'A002_0005','201206',4,5,4,'D002'
INSERT INTO T_CASH_FLOW SELECT 'A002_0006','201206',2,9,2,'D002'
/* 资产负债表 广东省汇总*/
SELECT ITEM_ID,YEAR_MONTH,SUM(START_QTY)START_QTY,SUM(END_QTY)END_QTY
FROM T_LIABILITY
WHERE YEAR_MONTH='201206'
GROUP BY ITEM_ID,YEAR_MONTH
/* 现金流量表 广东省汇总*/
SELECT ITEM_ID,YEAR_MONTH,SUM(MONTH_COUNT)MONTH_COUNT,SUM(LAST_YEAR_TOTAL)LAST_YEAR_TOTAL,SUM(PERIOD_TOTAL)PERIOD_TOTAL
FROM T_CASH_FLOW
WHERE YEAR_MONTH='201206'
GROUP BY ITEM_ID,YEAR_MONTH
相同的是都按ITEM_ID,YEAR_MONTH作分组,不同的是汇总的字段名和字段个数不一样。
求一存储过程,能根据不同的报表名称(物理表名),实现对需要的字段进行汇总。
参数1:例如:"T_LIABILITY"
参数2:例如:"MONTH_COUNT,LAST_YEAR_TOTAL,PERIOD_TOTAL"
------解决方案--------------------
不是沒人願意幫你,你的帖表達不是很清楚
比方說你要輸入的參數有哪些,還有對應不同的表你統計時如何知道某個字段是否是用來匯總的,還有你對於每個表的查詢倏件是如何的
至少你要先把意思表達很清楚
------解决方案--------------------
表名能作为过程的参数?真不懂是什么意思.............