基于mysql查询中最近日期的每个组的最大n

xoefb8l8 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(380)

我需要一些帮助来解决一个问题，下面是一个带有扫描数据的表，我希望我的sql查询只显示带有最新数据的记录 report_created 给定项目的记录。一个项目可以有多个 scan_id 下表中的示例8888\u super\u start只应显示一次最新报告创建时间，并且只应显示最新扫描的文件数。

+--------------------+------------------+---------------+---------------------+---------------------+
| scan_id            | project_name     | files_scanned |  scan_submitted     |  report_created     |
+--------------------+------------------+---------------+---------------------+---------------------+
| ba2468dd-2e38-478a | 8888_super_start | 123           | 2018-01-23 12:58:43 | 2018-01-23 13:48:46 |
| 5d3cb423-4cbb-4196 | 9111_kick_start  | 1040          | 2018-01-23 14:57:15 | 2018-01-23 15:58:33 |
| 75ff4cfd-172a-4f2d | 8888_super_start | 180           | 2018-05-25 14:37:33 | 2018-05-25 20:17:19 |
+--------------------+------------------+---------------+---------------------+---------------------+

我试过帖子中提到的方法，但效果不佳。请提供一些建议。
尝试

select sd.scan_id,sd.project_name,sd.files_scanned from blackbox.tb_cx_scan_details sd
left outer join blackbox.tb_cx_scan_details t2
on sd.scan_id = t2.scan_id
and (sd.report_created < t2.report_created
or (sd.report_created = t2.report_created ))
where t2.scan_id is NULL

sql mysql subquery greatest-n-per-group

来源：https://stackoverflow.com/questions/53564229/greatest-n-per-group-based-on-recent-date-in-mysql-query

2条答案

按热度按时间

ffx8fchx1#

一种方法是先分组 project_name 获取最新信息 report_created 对于每个组，您可以在适当的列上联接到这个新表，以获取每个项目的其余信息。

SELECT
    sd.scan_id,
    sd.project_name,
    sd.files_scanned
FROM
    blackbox.tb_cx_scan_details AS sd
INNER JOIN
    ( SELECT
          project_name, MAX(report_created) AS MaxReportCreated
      FROM
          blackbox.tb_cx_scan_details
      GROUP BY
          project_name ) AS latest ON latest.project_name = sd.project_name
                                   AND latest.MaxReportCreated = sd.report_created

赞(0）回复(0）举报 2021-06-18

xytpbqjk2#

在MySQL5.x中，可以使用子查询：

select * from tb_cx_scan_details
  where (project_name, report_created) in (
    select project_name, max(report_created) 
    from tb_cx_scan_details
    group by project_name
  )

在mysql 8.x中，可以使用窗口函数：

select *,
    row_number() over (partition by project_name 
                       order by report_created desc) as my_rank
  from tb_cx_scan_details
  where my_rank = 1

赞(0）回复(0）举报 2021-06-18

我来回答

基于mysql查询中最近日期的每个组的最大n

2条答案

相关问题

热门标签

最新问答