oracle 无关联字段的表之间关联
需求:计算A表中 开始日期到结束日期之间有几个交易日
(不计算开始当天)
如:
开始日期 | 结束日期 | 天数 |
---|---|---|
20211129 | 20211201 | 2 |
20211129 | 20211129 | 0 |
20211129 | 20211130 | 1 |
A表:
id | bdate | edate |
---|---|---|
1 | 20211126 | 20211129 |
2 | 20211129 | 20211129 |
3 | 20211129 | 20211130 |
4 | 20211129 | |
5 | 20211122 | 20211129 |
交易日表B:0–非交易日;1–交易日
dateFlag | bdate |
---|---|
… | … |
1 | 20211126 |
0 | 20211127 |
0 | 20211128 |
1 | 20211129 |
1 | 20211130 |
… | … |
解决:
select a.id ,
a.bdate,
a.edate,
case when a.bdate is null or a.edate is null
then null
else count(b.date) -1
end as cnt
from A a
left join B b
on b.dateFlag = '1'
and b.date between a.bdate and a.edate
group by a.id ,
a.bdate,
a.edate;