按日期和单独的标准计数数据

Question

我有以下2个表

TABLE1      
REFNO   XDATE       XNUM
123     01/01/2017  111
456     01/07/2017  111
789     01/01/2017  222
TABLE2  
YNUM    YDATE
111     03/01/2017
111     04/07/2017
222     03/01/2017
222     04/01/2017
Expected Result
REFNO XCOUNT
123   1
456   1
789   2

我想知道是否可以通过refno将此数据分组。使用上面的数据，refnos 123＆amp;456均使用XNUM 111，但由于Ydate 04/07/2017在2017年1月7日之后，它在Refno 456中计数。

所以，我正在寻找的是表2的ynum计数，基于相应的refno。

Answer 1

在这样的日期上获取匹配是棘手的。在其他数据库中，我会使用相关的子查询，但在Oracle中不容易起作用(因为相关子句嵌套得太深了(。

相反，让我们添加全日期范围并使用left join：

select t1.refno, count(t2.refno)
from table1 t1
     (select t2.*, lead(xdate) over (partition by refno order by xdate) as next_xdate
      from t2
     ) t1
     on t1.refno = t2.refno and
        t1.ydate >= t2.xdate and
        (t1.ydate < t2.next_xdate or t2.next_xdate is null)
group by t1.refno;