被sql所困，请sql高手指点

BETTER_ROOT

写个PROCEDURE把
很快就解决了

beginner-bj

我刚刚练了一下手，结合了shell script。不知道纯SQL或PROCEDURE怎么写？哪位高手能写一个？让我们新手学学。

db2 => select * from tab1

KEHU ZHANGHAO JINE      
---- -------- -----------
a    a1               100
a    a2               200
b    b1               300
b    b2               400

  4 record(s) selected.

db2 => select * from tab2

KEHU ZHUZHI
---- ------
a    ac   
b    bc   

  2 record(s) selected.

db2 => quit;
DB20000I  The QUIT command completed successfully.
$ cat >1.sql
with temp ( KEHU, ZHANGHAO, JINE) as ( select KEHU, ZHANGHAO, sum(JINE) from tab1 group by  rollup(KEHU, ZHANGHAO))
select temp.KEHU, tab2.ZHUZHI, ZHANGHAO, JINE from temp join tab2 on temp.KEHU=tab2.KEHU
order by KEHU, ZHANGHAO;
$ db2 -xtf 1.sql | xargs -n12 |awk 'BEGIN {print "客户 住址 帐号1 金额1 帐号2 金额2 总金额"} {print $1,$2,$3,$4,$7,$8,$12}'
客户 住址 帐号1 金额1 帐号2 金额2 总金额
a ac a1 100 a2 200 300
b bc b1 300 b2 400 700

beginner-bj

照搬照抄，我也写了个存储过程，这回算是入门了，呵呵
http://bbs.chinaunix.net/viewthr ... &extra=page%3D1

db2 create TABLE tab3 (KEHU varchar(5),ZHUZHI varchar(5),ZHANGHAO1 varchar(5),JINE1 int,ZHANGHAO2 varchar(5),JINE2 int, sum int)


CREATE PROCEDURE db2admin.test2 ( )
------------------------------------------------------------------------
--SQL 存储过程  
------------------------------------------------------------------------

Lable1: begin

------------------------------------------------------------------------
--定义变量
--v_NumOfRecd存放对应tab1每个KEHU记录条数
--v_Index控制当前记录是在KEHU相同的记录中第几条
--v_KEHU等三个变量用于存放临时数据
--at_end控制是否到底
------------------------------------------------------------------------
DECLARE SQLSTATE CHAR(5);
DECLARE v_NumOfRecd int;
DECLARE v_Index int;
DECLARE v_KEHU varchar(5);
DECLARE v_ZHANGHAO1 varchar(5);
DECLARE v_JINE1 int;
DECLARE v_ZHANGHAO2 varchar(5);
DECLARE v_JINE2 int;
DECLARE v_ZHUZHI varchar(5);
DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE C1 CURSOR FOR
select distinct(KEHU) from tab1;

DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
SET v_NumOfRecd = 2;

------------------------------------------------------------------------
--游标移动一次，就到一个新的KEHU，KEHU不会重复，因为经过上面的distinct
------------------------------------------------------------------------
OPEN C1;
Concat_Loop:
LOOP
FETCH C1 INTO v_KEHU;
IF at_end = 1 THEN
LEAVE Concat_Loop;
END IF;
------------------------------------------------------------------------
--遇到每个KEHU ，......
------------------------------------------------------------------------
SET v_Index=1;
SET v_ZHANGHAO1 = (SELECT ZHANGHAO FROM
(SELECT ROW_NUMBER() over() as a ,tab1.* FROM tab1 where KEHU = v_KEHU) as x where a=v_Index );
SET v_JINE1 = (SELECT JINE FROM
(SELECT ROW_NUMBER() over() as a ,tab1.* FROM tab1 where KEHU = v_KEHU) as x where a=v_Index );

SET v_Index=2;
SET v_ZHANGHAO2 = (SELECT ZHANGHAO FROM
(SELECT ROW_NUMBER() over() as a ,tab1.* FROM tab1 where KEHU = v_KEHU) as x where a=v_Index );
SET v_JINE2 = (SELECT JINE FROM
(SELECT ROW_NUMBER() over() as a ,tab1.* FROM tab1 where KEHU = v_KEHU) as x where a=v_Index );

SET v_ZHUZHI = (SELECT ZHUZHI FROM tab2 where KEHU = v_KEHU );

INSERT INTO tab3 VALUES (v_KEHU, v_ZHUZHI, v_ZHANGHAO1, v_JINE1, v_ZHANGHAO2, v_JINE2, v_JINE2+v_JINE1);

END LOOP Concat_Loop;
CLOSE C1;
END Lable1

C:\>db2 call test2
call test2

  返回状态 = 0



C:\>db2 select * from tab3
select * from tab3

KEHU  ZHUZHI ZHANGHAO1 JINE1       ZHANGHAO2 JINE2       SUM
----- ------ --------- ----------- --------- ----------- -----------
a     ac     a1                100 a2                200         300
b     bc     b1                300 b2                400         700

  2 条记录已选择。

pulf

帮你顶一下吧。

fubeans

db2 => select c.*,d.zhuzhi from (select distinct (a.kehu),a.zhanghao as zhanghao
1,a.jine,b.zhanghao as zhanghao2,b.jine as jine2,(a.jine+b.jine) as sum_jine fro
m tab1 a,tab1 b where a.kehu=b.kehu and a.zhanghao != b.zhanghao and a.zhanghao
>= b.zhanghao) c,tab2 d where c.kehu = d.kehu

DEHU ZHANGHAO1 JINE        ZHANGHAO2 JINE2       SUM_JINE    ZHUZHI
---- --------- ----------- --------- ----------- ----------- ------
a    a2                200 a1                100         300 ac
b    b2                400 b1                300         700 bc

  2 条记录已选择。

huyuhui001

建一个临时表，往里面填，呵呵，或许这是一个搜主意。

@龙井茶

横表变纵表，如果仅两列的话可以用我的方法，如果列数多了，确实用一条语句不容易处理。
/work_dev/test$cat aaa
create table acc_tmp (cust_id int, acc_id varchar(9), fee bigint );
insert into acc_tmp values( 123,'a1',100);
insert into acc_tmp values( 123,'a2',200);
insert into acc_tmp values( 456,'a1',300);
insert into acc_tmp values( 456,'a2',400);
create table addr_tmp (cust_id int, address varchar(19) );
insert into addr_tmp values( 123,'here');
insert into addr_tmp values( 456,'their');
/work_dev/test$cat bbb
with temp (row_id,cust_id,acc_id,fee,address )as (
select ROW_NUMBER() over() as a ,c.cust_id,c.acc_id,c.fee,d.address from acc_tmp c left outer join addr_tmp d on (c.cust_id=d.cust_id))
select a.cust_id,a.address,a.acc_id,a.fee,b.acc_id,b.fee,a.fee+b.fee as allfee from (select * from temp where mod(row_id,2)=1)as a,
(select * from temp where mod(row_id,2)=0) as b where a.cust_id=b.cust_id;
/work_dev/test$db2 -tvf bbb
with temp (row_id,cust_id,acc_id,fee,address )as ( select ROW_NUMBER() over() as a ,c.cust_id,c.acc_id,c.fee,d.address from acc_tmp c left outer join addr_tmp d on (c.cust_id=d.cust_id) order by c.cust_id) select a.cust_id,a.address,a.acc_id,a.fee,b.acc_id,b.fee,a.fee+b.fee as allfee from (select * from temp where mod(row_id,2)=1)as a, (select * from temp where mod(row_id,2)=0) as b where a.cust_id=b.cust_id

CUST_ID     ADDRESS            ACC_ID   FEE                  ACC_ID   FEE                  ALLFEE              
----------- ------------------ -------- -------------------- -------- -------------------- --------------------
        123 here               a1                        100 a2                        200                  300
        456 their              a1                        300 a2                        400                  700

  2 条记录已选择。

[ 本帖最后由 @龙井茶 于 2006-8-9 16:27 编辑 ]

beginner-bj

fubeans和@龙井茶的方法都不错，学到不少东西。

不过，假如每个帐号对应的记录数不同的情况下，如：有的1条，有的2条，有的3条……那就好象没有通用的方法了。

ddmmdd

sql适合得到这样的：
客户       住址   总金额