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同行回答
SELECT a.*, b.trandate2 FROM ( SELECT row_number() over(order by trand ...jamycym 发表于 2010-7-9 10:40
这个不错~~~~SELECT
a.*,
b.trandate2
FROM
(
SELECT
row_number() over(order by trand ...
jamycym 发表于 2010-7-9 10:40
这个不错~~~~收起
浏览726
给你个olap 函数参考:
select accto, bal, trandate,
coalesce(
min(trandate) over
(partition by accto order by to_date(trandate, 'YYYY-MM-DD HH24:MI:SS') nulls last rows between 1 following and 1 following),
'2010-12-31 00:00:00'
) next_trandate
from table(values
(102012011, 81.23, '2010-01-03 00:00:00'),
(102012011, 100.21, '2010-01-05 00:00:00'),
(102012011, 481.23, '2010-02-04 00:00:00')
)
as t(accto, bal, trandate)收起
select accto, bal, trandate,
coalesce(
min(trandate) over
(partition by accto order by to_date(trandate, 'YYYY-MM-DD HH24:MI:SS') nulls last rows between 1 following and 1 following),
'2010-12-31 00:00:00'
) next_trandate
from table(values
(102012011, 81.23, '2010-01-03 00:00:00'),
(102012011, 100.21, '2010-01-05 00:00:00'),
(102012011, 481.23, '2010-02-04 00:00:00')
)
as t(accto, bal, trandate)收起
浏览714
很好很强大，测试通过。:victory:
收起
回复 zihowe
select acctno,bal,trandate1,
LEAD(trandate1,1,'2012-12-31')
OVER(ORDER BY tranda ...
ninth 发表于 2010-7-12 14:06
浏览705
回复 16# zihowe
select acctno,bal,trandate1,
LEAD(trandate1,1,'2012-12-31')
OVER(ORDER BY trandate1) AS trandate2
from
(
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')
) as t(acctno,bal,trandate1)收起
select acctno,bal,trandate1,
LEAD(trandate1,1,'2012-12-31')
OVER(ORDER BY trandate1) AS trandate2
from
(
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')
) as t(acctno,bal,trandate1)收起
浏览751
浏览735