书名 价格 生效日 生效截止日
A 1.2 2017-04-1 2017-05-01
A 1.3 2017-05-01 2017-06-01
A 1.4 2017-06-01 null
B 1.2 2017-04-1 2017-05-01
B 1.3 2017-05-01 2017-06-01
B 1.4 2017-06-01 null想同时把书名A,B的最后记录的生效日更新为2017-05-01,同时上一条的截止日更新为2017-05-01
如下:A 1.2 2017-04-1 2017-05-01
A 1.3 2017-05-01 2017-05-01
A 1.4 2017-05-01 null
B 1.2 2017-04-1 2017-05-01
B 1.3 2017-05-01 2017-05-01
B 1.4 2017-05 -01 null请教大家如何写更新SQL,谢谢。
A 1.2 2017-04-1 2017-05-01
A 1.3 2017-05-01 2017-06-01
A 1.4 2017-06-01 null
B 1.2 2017-04-1 2017-05-01
B 1.3 2017-05-01 2017-06-01
B 1.4 2017-06-01 null想同时把书名A,B的最后记录的生效日更新为2017-05-01,同时上一条的截止日更新为2017-05-01
如下:A 1.2 2017-04-1 2017-05-01
A 1.3 2017-05-01 2017-05-01
A 1.4 2017-05-01 null
B 1.2 2017-04-1 2017-05-01
B 1.3 2017-05-01 2017-05-01
B 1.4 2017-05 -01 null请教大家如何写更新SQL,谢谢。
如果存在就好办了吧。
至于上一条的截止日期,可以用row_number() over(order by name desc ) 取行号为1的更新
先获取A,B的最后记录的生效日的ID
select test.ID from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test)select test.ID from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test)
再获取A,B 上一条的截止日的ID
select test.ID from test where test.name='A' and test.ID=(select test.ID from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test))-1select test.ID from test where test.name='B' and test.ID= (select test.ID from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test))-1最后用case when then
UPDATE t SET t.Stime=CASE WHEN t.ID=(select test.ID from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test)) THEN '2017-05-01' WHEN t.ID=(select test.ID from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test)) THEN '2017-05-01' ELSE t.Stime end
, t.Etime=case when t.ID=(select test.ID from test where test.name='A' and test.ID=(select test.ID from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test))-1
) then '2017-05-01' WHEN t.ID=(select test.ID from test where test.name='B' and test.ID= (select test.ID from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test))-1) THEN '2017-05-01' ELSE t.Etime end
FROM test AS t
WHERE ID IN(
SELECT ID FROM(
SELECT ID,NAME,PRICE,START_TIME,END_TIME,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY
START_TIME DESC)RN FROM BOOK
ORDER BY NAME,START_TIME DESC)
WHERE RN=1
)UPDATE BOOK SET END_TIME='2018-09-19'
WHERE ID IN(
SELECT ID FROM(
SELECT ID,NAME,PRICE,START_TIME,END_TIME,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY
START_TIME DESC)RN FROM BOOK
ORDER BY NAME,START_TIME DESC)
WHERE RN=2
)
(select *(select 书名,价格,生效日,截止日,row_number () over (partition by 书名 order by 生效日 desc ) rn from Table t ) where rn=1)
set 生效日=date'2017-05-01';Update
(select *(select 书名,价格,生效日,截止日,row_number () over (partition by 书名 order by 生效日 desc ) rn from Table t ) where rn=2)
set 截止日=date'2017-05-01';