今天遇到这样个需求现在有3张表T1T2T3按定条件将T1、T2联合查询的后结果 as A左连接T3根据ID分组、A.NAME排序将最终结果以WORD格式显示

WORD效果如下:

、NAME1

1、RESULT11

2、RESULT12

3、RESULT13

n、RESULT1n

2、NAME2

1、RESULT21

2、RESULT12

3、RESULT13

.

.

.

m、RESULT1m

用存储过程生成行号

create procedure auto_info_published

begin

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE R_CODE INTEGER DEFAULT 0;
DECLARE brand_id DECIMAL(10);
declare tempBrandId decimal(10);

DECLARE BRAND_ID_CURSOR CURSOR FOR
select distinct sellor.BRAND_ID
from AUTO_SELLOR as sellor, ZX_WF_INSTANCE ,(select BRAND_ID from AUTO_BRAND as a , ZX_WF_INSTANCE as b
where BUSINESS_TYPE='1' and BRAND_ID = BUSI_ID and MODEL_CODE = 'MX001' and (ACTIVITY_CODE='JD004' or ACTIVITY_CODE='JD005')
and INSTANCE_STATE = 2 ) as c
where sellor.BRAND_ID=c.BRAND_ID and BUSINESS_TYPE='1' and SELLOR_ID = BUSI_ID and MODEL_CODE = 'MX003'
and ACTIVITY_CODE='JD004' and INSTANCE_STATE = 2;

open BRAND_ID_CURSOR;
Fetch BRAND_ID_CURSOR o brand_id;
R_CODE=SQLCODE;
while (R_CODE <> 100) do
tempBrandId = brand_id; ------这句很关键如果直接是insert.....values；这句可以不要insert.....select....；如果没有这句虽然循环次数没有错但将游标中所有brand_id取出来insert不能实现游标次只能取个值功能

insert o AUTO_SELLOR_PUBLISHED( SELLORPUBLISHED_ID, SELLOR_ID, BRAND_ID, SELLOR_NAME, REGISTRATION, SELLOR_PID,
ADDRESS, TRD_SCOPE, LINKMAN, TELEPHONE, MOBILE_TEL, START_DATE, END_DATE, INVALID_DATE, OFFICIAL_SYMBOL,
SELLOR_NUM_LIST, AREA, PROVINCE, BUSINESS_TYPE )
select nextval for SEQ, SELLOR_ID, BRAND_ID, SELLOR_NAME, REGISTRATION, SELLOR_PID,
ADDRESS, TRD_SCOPE, LINKMAN, TELEPHONE, MOBILE_TEL, START_DATE, END_DATE, INVALID_DATE, offSymbol,
rtrim(char(rownumber over(order by SELLOR_NAME))), AREA, PROVINCE, BUSINESS_TYPE
from AUTO_SELLOR ,ZX_WF_INSTANCE
where BRAND_ID = tempBrandId and BUSINESS_TYPE='1' and SELLOR_ID = BUSI_ID and MODEL_CODE = 'MX003' and ACTIVITY_CODE='JD004'
and INSTANCE_STATE = 2 ;

Fetch BRAND_ID_CURSOR o brand_id;
R_CODE=SQLCODE;
end while;
close BRAND_ID_CURSOR;
------nextval for SEQ:为DB2中ID通过sequents生成时insert值写法；

------rtrim(char(rownumber over(order by SELLOR_NAME))):为DB2中行号生成思路方法