两个日期之间的值应该为 0？

发布于 2024-08-07 14:40:54 字数 648 浏览 5 评论 0原文

使用 SQL Server 2000

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

Table1

ID FromDate ToDate

001 20090801 20090815
002 20090817 20090820
…,

Table2

Id Date Value

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

从上面两个表中我想要 ID、Date、table2 中的值，其中 table2 .date 介于 table1.fromdate 和 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

如何针对此条件进行查询？

原文

Using SQL Server 2000

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

Table1

ID FromDate ToDate

001 20090801 20090815
002 20090817 20090820
…,

Table2

Id Date Value

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

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

Expected Output

Id Date Value

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

001 20090815 0
001 20090816 250

How to make a query for this condition?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最佳男配角 2024-08-14 14:40:55

这将显示存在的记录的 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，< code>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

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

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 原文

陌路黄昏 2024-08-14 14:40:55

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

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

回复收藏 0 原文

~没有更多了~