22,210
社区成员
发帖
与我相关
我的任务
分享
A.valueB = A.valueA / B.valueB where A.code = B.code and B.date <= A.date
但是 B.date <= A.date 需要的是 select top 1 B.date from B left join A on A.code = B.code
where B.date <= A.date order by date desc
这样的update语句怎么写udpate A set A.valueB =
case when exists (select * from B where A.code = B.code and A.date >= B.date)
then select top 1 valueB from B where A.code = B.code and A.date >= B.date order by B.date desc
else select top 1 valueB from B where A.code = B.code order by B.date
end
select top 1 b.col_value from b where a.id = b.id and a.date >= b.date
就采用 select top 1 b.col_value from b where a.id = b.id order by hqdate
这个怎么能够联合起来呢?-- 这个意思吗?
update a
set a.col = (select top 1 b.col_value from b where a.id = b.id and a.date > b.date)
DECLARE @d DATETIME='10/3/2016'
;WITH b(ID,value,d,hqdate) AS
(
SELECT 1,1,CONVERT(DATETIME,'10/1/2016'),CONVERT(DATETIME,'8/1/2016') UNION ALL
SELECT 1,2,'10/2/2016','8/4/2016' UNION ALL
SELECT 1,3,'10/3/2016','8/9/2016' UNION ALL
SELECT 1,4,'10/4/2016','8/16/2016' UNION ALL
SELECT 1,5,'10/5/2016','8/29/2016'
)
SELECT * FROM (
SELECT *
,ROW_NUMBER() OVER (ORDER BY hqdate ) AS hq_rn
,ROW_NUMBER() OVER (ORDER BY CASE WHEN b.d <= @d THEN d ELSE NULL END desc ) AS rn
,COUNT(CASE WHEN b.d <= @d THEN d ELSE NULL END) OVER ( PARTITION BY id ) AS EaryCount
FROM b
) c WHERE 1=CASE WHEN c.EaryCount=0 THEN c.hq_rn ELSE rn END
-- 那就把满足条件的,放在前面;
select top 1 valueB from B where A.code = B.code
order by case when A.date >= B.date then 0 else 1 end,b.date