IT分销/经销 数据库
能用sql实现吗？请求db2高人得帮助
表1acctno bal trandate --------------------------------------------102012011 81.23 2010-01-03102012... 显示全部
查看其它 20 个回答alleachine 的回答
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;