SQL连接两个表，仅获取第二个表的最新条目 [英] SQL Join two tables, only get latest entry of second table

问题描述

我有这两个表:

项目

itemname  description  belongs
A1        some_text    user1
A2        some_text    user1
A3        some_text    user1
A4        some_text    user1
A5        some_text    user1
A1        some_text    user2
B2        some_text    user2

动作

itemname  start_date  end_date    belongs
A1        2013-02-01  2014-01-12  user1
A1        2014-08-14  NULL        user1
A1        2014-10-15  2015-01-01  user1
A2        2013-08-03  2014-08-14  user1     
A2        2014-08-14  NULL        user1    
A3        2013-08-02  2014-08-20  user1
A3        2013-12-05  2014-01-07  user1
A4        2013-07-15  2014-09-13  user1
A4        2014-09-13  NULL        user1
A5        2013-07-15  2014-09-13  user1
A5        2015-03-11  2016-03-12  user1
A5        2016-03-12  2016-04-13  user1
A1        2015-08-01  2015-08-12  user2
B2        2015-08-13  2015-08-23  user2

我当时正在研究join和max(date)，但没有找到可行的解决方案.

I was playing around with joins and max(date) but didn't come to a working solution.

user1的结果应如下所示:

A result for user1 should looks like this:

itemname  description  belongs  start_date  end_date
A1        some_text    user1    2014-08-14  NULL        
A2        some_text    user1    2014-08-14  NULL       
A3        some_text    user1    2013-08-02  2014-08-20      
A4        some_text    user1    2014-09-13  NULL              
A5        some_text    user1    2016-03-12  2016-04-13       

如果没有行，我需要end_date(最新，最新)最高的行(运动):

I need the line (movement) with the highest (latest, newest) end_date if there is no line where:

end_date = NULL

如果有一行end_date = NULL，则该行需要该行.

If there is a line where end_date = NULL, I need this line for that item.

这里的难点在于，对于max(start_date)的排序是行不通的，因为有时一个项目的另一个时间周期内会有一个时间周期.

Difficulty here is that sorting for max(start_date) would not work since sometimes there is a timeperiod inside another timeperiod for one item.

希望您能理解我的问题.

I hope you could understand my problem.

来自德国的问候:)

推荐答案

您需要类似的东西

找到每个itemname&的最近开始日期. belongs组合，然后以最大开始日期将结果重新加入以得到结果

Find the most recent start date for each itemname & belongs combination then join the result back with max start date to get the result

SELECT i.itemname,i.description,i.belongs,m.start_date,m.end_date
FROM   items i
       JOIN movements m
         ON i.itemname = m.itemname
            AND i.belongs = m.belongs
       JOIN (SELECT itemname,
                    belongs,
                    Max(COALESCE(end_date, start_date)) AS max_dt,
                    Max(end_date)                       AS max_end_dat,
                    Max(start_date)                       AS max_start_dat
             FROM   movements
             GROUP  BY itemname,
                       belongs) m1
         ON m1.itemname = m.itemname
            AND m1.belongs = m.belongs
            AND ( ( m.end_date = m1.max_dt
                    AND m1.max_dt = m1.max_start_dat )
                   OR ( m1.max_dt = COALESCE(end_date, m.start_date)
                        AND m1.max_start_dat <> m1.max_dt )
                   OR ( m1.max_dt = m.start_date
                        AND m1.max_end_dat <> m1.max_dt ) )
ORDER  BY i.belongs,
          i.itemname 

• SQL FIDE DEMO

更新:

SELECT i.itemname,
       i.description,
       i.belongs,
       m.start_date,
       m.end_date
FROM   items i
       JOIN movements m
         ON i.itemname = m.itemname
            AND i.belongs = m.belongs
       JOIN (SELECT itemname,
                    belongs,
                    Max(start_date) AS max_dat,
                    'st'            AS indi
             FROM   movements
             WHERE  end_date IS NULL
             GROUP  BY itemname,
                       belongs
             UNION ALL
             SELECT itemname,
                    belongs,
                    Max(end_date) AS max_dat,
                    'ed'
             FROM   movements m
             WHERE  NOT EXISTS (SELECT 1
                                FROM   movements m1
                                WHERE  m.itemname = m1.itemname
                                       AND m.belongs = m1.belongs
                                       AND end_date IS NULL)
             GROUP  BY itemname,
                       belongs) m1
         ON m1.itemname = m.itemname
            AND m1.belongs = m.belongs
            AND ( ( m1.max_dat = m.start_date
                    AND indi = 'st' )
                   OR ( m1.max_dat = m.end_date
                        AND indi <> 'st' ) )
ORDER  BY i.belongs,
          i.itemname 

• SQL FIDE DEMO

如果您的RDBMS支持ROW_NUMBER窗口功能或APPLY运算符

This will be really easy if your RDBMS supports ROW_NUMBER window function or APPLY operator

这篇关于SQL连接两个表，仅获取第二个表的最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！