sql—如何“最好”在未关闭的已提交报表的最新报表更新日期之前返回sqlite记录

zzlelutf  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(170)

我有两个表,一个带有“reports”,另一个带有“report updates”。我想显示尚未关闭的报表的最新更新日期,以及其他“报表”信息。
我见过类似的问题,但不确定走那条路是不是最好的办法。我试过用一张table来做,我想我已经成功了,但两张table似乎是正确的方法。我将sqlite与Python3.7结合使用。
表:[报告]

+------------------+------------+----------------------------+--------------------------+  
| ClientName       | Report_ID  | Submission_Date            | Closed_Date              |  
+------------------+------------+----------------------------+--------------------------+  
| JoneBob          | 10010      | 2014-12-09 11:53:40.423    |                          |  
| TrumHar          | 10011      | 2014-11-09 11:53:40.423    |                          |  
| JoneBob          | 10012      | 2014-10-09 11:53:40.423    | 2014-10-20 04:00:00.000  |  
+------------------+------------+----------------------------+--------------------------+

表:[报告更新]。

+------------------+----------------------------+
| Report_ID        | Follow_Up_Date             | 
+------------------+------------+---------------+
| 10010            | 2014-12-10 11:53:40.423    | 
| 10011            | 2014-11-11 11:53:40.423    |
| 10012            | 2014-10-14 11:53:40.423    |
| 10010            | 2014-12-12 11:53:40.423    | 
| 10011            | 2014-11-14 11:53:40.423    |
| 10012            | 2014-10-18 11:53:40.423    | 
| 10010            | 2014-12-13 11:53:40.423    | 
| 10011            | 2014-11-15 11:53:40.423    |
| 10012            | 2014-10-21 11:53:40.423    | 
+------------------+----------------------------+

预期结果:

+------------------+------------+----------------------------+--------------------------+  
| ClientName       | Report_ID  | Submission _Date           | Follow_Up_Date           |  
+------------------+------------+----------------------------+--------------------------+  
| JoneBob          | 10010      | 2014-12-09 11:53:40.423    | 2014-12-13 11:53:40.423  |
| TrumHar          | 10011      | 2014-11-09 11:53:40.423    | 2014-11-15 11:53:40.423  |
+------------------+------------+----------------------------+--------------------------+
f1tvaqid

f1tvaqid1#

你需要最大的 Follow_Up_Date 分组依据 ClientName 以及 Report_ID :

SELECT ClientName, r.Report_ID, 
       MIN(Submission_Date) AS Submission_Date, 
       MAX(Follow_Up_Date) AS Follow_Up_Date
  FROM Reports r
  JOIN Report_Updates ru
    ON ru.ClientName = r.ClientName    
 WHERE Closed_Date IS NULL   
 GROUP BY ClientName, Report_ID

相关问题