两个日期之间的值应该是 0? [英] Value should be 0 between the two dates?

问题描述

使用 SQL Server 2000

Using SQL Server 2000

我想比较table1.from、table1.todate之间的table2.date，如果存在则值应该是0(零)

I want to compare the table2.date between table1.from, table1.todate, if exist then value should be 0 (zero)

表 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
…

从上面两个表中我想从 table2 中 ID、日期、值，其中 table2.date 介于 table1.fromdate 和 table1.todate 之间，然后 table2.value =0

From the above two table I want to ID, Date, value from table2 where table2.date between table1.fromdate and table1.todate then table2.value =0

预期输出

Id Date Value

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

001 20090815 0
001 20090816 250

如何查询这个条件?

推荐答案

This will show value 表示存在的记录，0 表示缺失的记录:

This will show value for the records present, 0 for the records missing:

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 表示缺失的记录:

This will work other way around: 0 for the records present, value for the records missing:

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

这篇关于两个日期之间的值应该是 0?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！