值应该是两个日期之间0？(Value should be 0 between the two dat

使用SQL Server 2000

我想比较table1.from，table1.todate之间的table2.date，如果存在，则值应为0（零）

表格1

ID FromDate ToDate

001 20090801 20090815
002 20090817 20090820
…,

表2

Id Date Value

001 20090730 100
001 20090731 200
001 20090801 300
001 20090802 400
…
001 20090815 0
001 20090816 250
…

从上述两个表欲ID，日期，数值从表2，其中table1.fromdate和table1.todate之间table2.date然后table2.value = 0

预计输出

Id Date Value

001 20090730 100
001 20090731 200
001 20090801 0
001 20090802 0
…

001 20090815 0
001 20090816 250

如何使这个状态查询？

Answer 1:

这将显示value对当前的记录， 0为记录丢失：

SELECT  t2.id, t2.date,
        COALESCE(
        (
        SELECT  TOP 1 t2.value
        FROM    table1 t1
        WHERE   t2.date BETWEEN t1.fromdate AND t1.todate
                AND t2.id = t1.id
        ), 0) AS value
FROM    table2 t2

这将解决其他的方式： 0为当前记录， value的记录丢失：

SELECT  t2.id, t2.date,
        COALESCE(
        (
        SELECT  TOP 1 0
        FROM    table1 t1
        WHERE   t2.date BETWEEN t1.fromdate AND t1.todate
                AND t2.id = t1.id
        ), t2.value) AS value
FROM    table2 t2

Answer 2:

select t2.id, t2.date, coalesce(T.value, 0) value
from table2 t2
left join 
     (select t22.id, t22.date, t22.value
        from table2 t22
       where not exists (select null from table1 t1
                          where t22.date between t1.fromdate and t1.todate)
     ) T on t2.id = T.id and t2.date = T.date

文章来源: Value should be 0 between the two dates?