IT分销/经销数据库
能用sql实现吗？请求db2高人得帮助
表1
acctno bal trandate
--------------------------------------------
102012011 81.23 2010-01-03
102012011 100.21 2010-01-05
102012011 481.23 2010-02-04
102012011 1300.21 2010-04-05
102012011 281.23 2010-04-07
102012011 1020.21 2010-05-01
102012011 821.23 2010-05-21
102012011 100.21 2010-05-26
表二
acctno bal trandate trandate2
------------------------------------------------------
102012011 81.23 2010-01-03 2010-01-05
102012011 100.21 2010-01-05 2010-02-04
102012011 481.23 2010-02-04 2010-04-05
102012011 1300.21 2010-04-05 2010-04-07
102012011 281.23 2010-04-07 2010-05-01
102012011 1020.21 2010-05-01 2010-05-21
102012011 821.23 2010-05-21 2010-05-26
102012011 100.21 2010-05-26 2012-12-31
表一到表二的变换能用sql实现吗，如何解决这个问题请求db2高人的指导
acctno bal trandate
--------------------------------------------
102012011 81.23 2010-01-03
102012011 100.21 2010-01-05
102012011 481.23 2010-02-04
102012011 1300.21 2010-04-05
102012011 281.23 2010-04-07
102012011 1020.21 2010-05-01
102012011 821.23 2010-05-21
102012011 100.21 2010-05-26
表二
acctno bal trandate trandate2
------------------------------------------------------
102012011 81.23 2010-01-03 2010-01-05
102012011 100.21 2010-01-05 2010-02-04
102012011 481.23 2010-02-04 2010-04-05
102012011 1300.21 2010-04-05 2010-04-07
102012011 281.23 2010-04-07 2010-05-01
102012011 1020.21 2010-05-01 2010-05-21
102012011 821.23 2010-05-21 2010-05-26
102012011 100.21 2010-05-26 2012-12-31
表一到表二的变换能用sql实现吗，如何解决这个问题请求db2高人的指导
21同行回答
V8.1下：
DROP TABLE BACK.test_2;
CREATE TABLE BACK.TEST_2
(
ACCTNO CHAR (10) NOT NULL WITH DEFAULT '',
BAL DECIMAL(19,4) NOT NULL WITH DEFAULT 0,
TRANDATE CHAR (10) NOT NULL WITH DEFAULT '',
TRANDATE2 CHAR (10) NOT NULL WITH DEFAULT '',
CONSTRAINT P_KEY_1 PRIMARY KEY (ACCTNO, TRANDATE)
);
DELETE FROM BACK.TEST_2 ;
INSERT INTO BACK.TEST_2 (ACCTNO,BAL,TRANDATE)
VALUES
('102012011', 81.23, '2010-01-03'),
('102012011', 100.21, '2010-01-05'),
('102012011', 481.23, '2010-02-04'),
('102012011', 1300.21, '2010-04-05'),
('102012011', 281.23, '2010-04-07'),
('102012011', 1020.21, '2010-05-01'),
('102012011', 821.23, '2010-05-21'),
('102012011', 100.21, '2010-05-26');
SELECT A.ACCTNO, A.BAL, A.TRANDATE, VALUE(MIN(B.TRANDATE), '2010-12-31') AS TRANDATE2
FROM BACK.TEST_2 A LEFT JOIN BACK.TEST_2 B
ON A.ACCTNO = B.ACCTNO AND A.TRANDATE < B.TRANDATE
GROUP BY A.ACCTNO, A.BAL, A.TRANDATE
ORDER BY A.ACCTNO ,A.TRANDATE;收起
DROP TABLE BACK.test_2;
CREATE TABLE BACK.TEST_2
(
ACCTNO CHAR (10) NOT NULL WITH DEFAULT '',
BAL DECIMAL(19,4) NOT NULL WITH DEFAULT 0,
TRANDATE CHAR (10) NOT NULL WITH DEFAULT '',
TRANDATE2 CHAR (10) NOT NULL WITH DEFAULT '',
CONSTRAINT P_KEY_1 PRIMARY KEY (ACCTNO, TRANDATE)
);
DELETE FROM BACK.TEST_2 ;
INSERT INTO BACK.TEST_2 (ACCTNO,BAL,TRANDATE)
VALUES
('102012011', 81.23, '2010-01-03'),
('102012011', 100.21, '2010-01-05'),
('102012011', 481.23, '2010-02-04'),
('102012011', 1300.21, '2010-04-05'),
('102012011', 281.23, '2010-04-07'),
('102012011', 1020.21, '2010-05-01'),
('102012011', 821.23, '2010-05-21'),
('102012011', 100.21, '2010-05-26');
SELECT A.ACCTNO, A.BAL, A.TRANDATE, VALUE(MIN(B.TRANDATE), '2010-12-31') AS TRANDATE2
FROM BACK.TEST_2 A LEFT JOIN BACK.TEST_2 B
ON A.ACCTNO = B.ACCTNO AND A.TRANDATE < B.TRANDATE
GROUP BY A.ACCTNO, A.BAL, A.TRANDATE
ORDER BY A.ACCTNO ,A.TRANDATE;收起
浏览402