SQL Server中两个表之间的数据比较

I have a requirement where I have two tables tbl_bb and tbl_rr and data in them like this:

我有一个要求,我有两个表tbl_bb和tbl_rr,其中的数据如下:

tbl_bb:

isin    agency  rating  date
----------------------------------
142356  MDA     A       2010-01-20
142356  MDA     AA      2012-05-14
142356  MDA     BB      2013-07-04

tbl_rr:

isin    agency  rr_rating   rr_date
------------------------------------
142356  MDA     A         2010-01-15
142356  MDA     AA+       2009-05-30
142356  MDA     BBB       2013-07-04

Now, requirement is I have to pick each and every record from tbl_bb and loop through the data in tbl_rr based on joining of isin and agency and the condition for looping is consider date from tbl_bb and check every date from tbl_rr and pick the closest date to that of tbl_bb date and other condition is rr_date(tbl_rr) <= date (tbl_bb)

现在,要求是我必须从tbl_bb中选择每一条记录并根据isin和agency的加入循环遍历tbl_rr中的数据,并且循环的条件是从tbl_bb考虑日期并检查tbl_rr的每个日期并选择最接近的日期到tbl_bb的日期和其他条件是rr_date(tbl_rr)<= date(tbl_bb)

Output:

isin    agency  rating  date     rr_rating  rr_date
-------------------------------------------------------
142356  MDA       A     2010-01-20    A     2010-01-15
142356  MDA       AA    2012-05-14    AA+   2010-01-15
142356  MDA       BB    2013-07-04    BBB   2013-07-04

Thanks!!

3 个解决方案

#1

I would do this using APPLY to get the closest 1 record from tbl_rr:

我会使用APPLY从tbl_rr获取最接近的1条记录:

SELECT  bb.isin,
        bb.agency,
        bb.rating,
        bb.date,
        rr.rr_rating, 
        rr.rr_date
FROM    tbl_bb AS bb
        OUTER APPLY
        (   SELECT  TOP 1 rr.rr_rating, rr.rr_date
            FROM    tbl_rr AS rr
            WHERE   rr.isin = bb.isin
            AND     rr.agency = bb.agency
            AND     rr.rr_date <= bb.date
            ORDER BY rr.rr_date DESC
        ) AS rr;

FULL WORKING EXAMPLE

完整的工作示例

-- SAMPLE DATA
WITH tbl_bb AS
(   SELECT  isin, agency, rating, date
    FROM    (VALUES
                (142356,'MDA','A','2010-01-20'),
                (142356,'MDA','AA','2012-05-14'),
                (142356,'MDA','BB','2013-07-04')
            ) AS bb(isin, agency, rating, date)
), tbl_rr AS
(   SELECT  isin, agency, rr_rating, rr_date
    FROM    (VALUES
                (142356,'MDA','A','2010-01-15'),
                (142356,'MDA','AA+','2012-05-30'),
                (142356,'MDA','BBB','2013-07-04')
            ) AS rr (isin, agency, rr_rating, rr_date)
)
-- SAMPLE DATA END
SELECT  bb.isin,
        bb.agency,
        bb.rating,
        bb.date,
        rr.rr_rating, 
        rr.rr_date
FROM    tbl_bb AS bb
        OUTER APPLY
        (   SELECT  TOP 1 rr.rr_rating, rr.rr_date
            FROM    tbl_rr AS rr
            WHERE   rr.isin = bb.isin
            AND     rr.agency = bb.agency
            AND     rr.rr_date <= bb.date
            ORDER BY rr.rr_date DESC
        ) AS rr;

#2

What I would do is I would join these tables as given in description, find DATEDIFF between two dates and then just find lowest DATEDIFF value for each ISIN and agency

我要做的是我会按照说明中的说明加入这些表,在两个日期之间找到DATEDIFF,然后找到每个ISIN和代理商的最低DATEDIFF值

#3

As per my assumption we can proceed like this if you want filter the data we can achieve like

根据我的假设,如果你想过滤我们可以实现的数据,我们就可以这样做

C.RN - 1 = CC.RR or   C.RN - 1 > CC.RR OR C.RN - 1 < CC.RR

Code:

  declare @t table (ISIN INT,Agency varchar(10),Rating varchar(5),dated date)
    insert into @t (ISIN,Agency,Rating,dated)values (142356,'MDA','A','2010-01-20'),
    (142356,'MDA','AA','2012-05-14'),
    (142356,'MDA','BB','2013-07-04')


    declare @tt table (ISIN INT,Agency varchar(10),Rating varchar(5),dated date)
    insert into @tt (ISIN,Agency,Rating,dated)values (142356,'MDA','A','2010-01-15'),
    (142356,'MDA','AA+','2012-05-30'),
    (142356,'MDA','BBB','2013-07-04')


    ;with cte as 
    (select ISIN,
                Agency,
                Rating,
                dated,
    ROW_NUMBER()OVER(ORDER BY ISIN)RN 
    from @t)
    , cte1 as 
    (select 
            ISIN,
            Agency,
            Rating,
            dated,
    ROW_NUMBER()OVER(ORDER BY ISIN)RR 
    from @tt)
    select C.ISIN,C.Agency,
            C.Rating,C.dated,
            CC.Agency,CC.Rating,
            CC.dated from cte c
            INNER JOIN cte1 CC
            ON C.RN = CC.RR

#1