查找第一个表中与第二个表的行的 ID 与最大日期匹配的行数 [英] Find count of rows in first table that match ids of row of second table with max date

问题描述

我有 2 张桌子:

表 1:

• id

表 2:

• id
• 日期

我需要找到 t1 的行数，以便对于所有 id 为 t1 且与 t2 id 匹配的行，选择最大日期为 t2 的行.

I need to find the count of rows of the t1 such that, for all rows with id of t1 that match ids of t2, select row with max date of t2.

最终计数应仅包含满足最大日期 t2 > 30 天的行.

Final Count should include only rows that satisfy max date of t2 > 30 days from present date.

尝试的查询:

SELECT COUNT(*)
FROM t1 
INNER JOIN t2 
ON t2.id = (SELECT id FROM t2 WHERE id = t1.id ORDER BY t2.date DESC LIMIT 1);

SELECT COUNT(*)
FROM t2
INNER JOIN t1 ON t1.id=t2.id
GROUP BY t1.id, t2.date;

注意:我所尝试的查询与我所知道的最终查询的外观相差甚远.我尝试将问题分解为中间部分，例如尝试使用匹配的 t2 id 获取 t1 的所有行，然后尝试仅获取最新的行等.

Note: The queries that I tried are no where near what the final query would look like, that I'm aware. I tried breaking the problem into intermediate parts, such as tried fetching all the rows of t1 with matched ids of t2, then I tried fetching only the latest one etc.

我在从第一个表中获取行时遇到问题，但有条件地将 id 匹配到第二个表.我无法从 SQL 的角度思考.

I'm having trouble thinking in terms of fetching rows from 1st table, but conditionally matching ids to the 2nd table. Im not being able to think in terms of SQL.

推荐答案

Select      Count(T1.id)
From        Table1 T1
            inner join
            Table2 T2
            On T1.id = T2.id
where       T2.Date = ( Select  Max(Date)
                        from    Table2
                        where   Date > DATEADD(dd,30,cast(getdate() as date)))

这篇关于查找第一个表中与第二个表的行的 ID 与最大日期匹配的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！