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

zzlelutf 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(169)

我有两个表，一个带有“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  |
+------------------+------------+----------------------------+--------------------------+

sql sqlite

来源：https://stackoverflow.com/questions/62307433/how-best-to-return-sqlite-records-by-most-recent-date-of-report-updates-for-su

1条答案

按热度按时间

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

赞(0）回复(0）举报 2021-07-29

我来回答

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

1条答案

相关问题

热门标签

最新问答