从查找表中获取每种类型和每个日期的最大ID

Question

我希望能够保留每个日期（Date）每个类型（Types）最高的报告编号（Report_ID）。

注：数据列有多个日期，下面仅显示了2021年1月1日。

问题：t1是我需要使用的查找表，我的难题在于它没有包含参考日期列。

select t2.*
from t2
where t1.Report_ID = (select max(t1.Report_ID)
                     from t1
                     where t2.Date = ??? and t2.Types = ???
                    );

t1

报告ID	名称	数值
1	名称 1	数值 1
2	名称 2	数值 2
3	名称 3	数值 3

t2

日期	类型	报告ID	名称
01.01.2020	类型 1	1	名称 1
01.01.2020	类型 1	2	名称 2
01.01.2020	类型 3	3	名称 3

视图

日期	类型	名称	数值	报告ID
01.01.2020	类型 1	名称 2	数值 2	2
01.01.2020	类型 3	名称 3	数值 3	3

- MoeAmine

---

请在您的问题中标记您正在使用的数据库。 - Gordon Linoff

它明确地说了 SQL。 - MoeAmine

请勿发布数据的图像（https://meta.stackoverflow.com/q/285551）。您可以查看SQL标签维基（https://stackoverflow.com/tags/sql/info）以了解如何创建可回答的SQL问题。 - astentx

@astentx 已经更正。谢谢。 - MoeAmine

@MoeAmine，由于两个表中都有report_id，所以你不需要在t1中使用日期列作为参考。 - Kazi Mohammad Ali Nur Romel

Answer 1

使用这个查询：

SELECT Date, Types, MAX(Report_ID) Report_ID
FROM t2
GROUP BY Date, Types

针对每个Date和Types，获取最大的Report_ID

将其与t1进行连接：

SELECT t2.Date, t2.Types, t1.Name, t1.Value, t1.Report_ID
FROM t1 
INNER JOIN (
  SELECT Date, Types, MAX(Report_ID) Report_ID
  FROM t2
  GROUP BY Date, Types
) t2 ON t2.Report_ID = t1.Report_ID

请点击 示例。
结果：

日期	类型	名称	数值	报告编号
2020-01-01	类型 1	名称 2	数值 2	2
2020-01-01	类型 3	名称 3	数值 3	3

- forpas

Answer 2

使用 ROW_NUMBER()：

WITH cte AS (
  SELECT t2.*, t1.Value, 
         ROW_NUMBER() OVER(PARTITION BY `Date`, Types ORDER BY Report_ID DESC) AS rn
  FROM t2
  JOIN t1 ON t1.Report_ID = t2.Report_ID
)
SELECT * FROM cte WHERE rn = 1;

db<>fiddle演示

- Lukasz Szozda

Answer 3

这是对原问题的回答。

我想保留每个日期（Date）的每种类型（Types）的最高报告ID（Report_ID）

这不需要参考表。您的逻辑应该在子查询中使用t2来实现您想要的功能：

select t2.*
from t2
where t2.Report_ID = (select max(tt2.Report_ID)
                      from t2 tt2
                      where tt2.Date = t2.date and tt2.Type = t2.Type
                     );

- Gordon Linoff

---

抱歉，我误操作了，在t1中添加了一个类型。实际上它并没有。 - MoeAmine

Answer 4

您可以使用以下语法来使用NOT EXISTS：

select t2.*
  from t2 
  --join t1 on t1.Report_ID = t2.Report_ID -- use it if you want data from t1 in SELECT 
where not exists 
      (select 1 from t2 t22 
        where t22.date = t2.date and t22.type = t2.type 
          and t22.Report_ID > t2.Report_ID) 

- Popeye

---

抱歉，我误操作了，在t1中添加了一个类型。实际上它并没有。 - MoeAmine

Answer 5

你可以通过使用row_number()和CTE轻松实现。首先，我们需要连接t1和t2以从t1获取value列。我们使用row_number()为给定日期中特定类型的每一行从最高Report_ID开始放置序列号。 然后，我们只考虑具有最低序列号的行，这代表了给定日期内任何特定类型的最高report_id。

With cte as
  (
select t2.date,t2.types,t2.report_id,t2.name ,t1.value ,row_number () over (partition by date,types order by t2.report_id desc) RowNumber 
    from t2 inner join  t1 on t2.report_id=t1.report_id 
  )
  select  date_format(date,"%Y.%m.%d") date,types,name,value,report_id from cte where RowNumber=1
  

输出：

- Kazi Mohammad Ali Nur Romel