SQL查询，选择每组值最大的一行

I'm very new to SQL and this one has me stumpted. Can you help me out with this query?

我对SQL很不熟悉，这一篇已经难住我了。你能帮我解决这个问题吗?

I have the following 2 tables:

我有以下两张表:

TABLE 1: IssueTable

表1:IssueTable

Id   | RunId | Value
---
1    | 1     | 10  
2    | 1     | 20  
3    | 1     | 30  
4    | 2     | 40  
5    | 2     | 50  
6    | 3     | 60 
7    | 4     | 70 
8    | 5     | 80 
9    | 6     | 90

TABLE 2: RunTable

表2:RunTable

RunId     | EnvironmentId
---
1         | 1
2         | 3
3         | 1
4         | 2
5         | 4
6         | 2

I need the IssueTable rows that represent the Max RunId grouped by the EnvironmentId in the RunTable. The result I would need from the tables is:

我需要可发布的行，它们表示根据RunTable中环境分组的最大RunId。我需要从表中得到的结果是:

EXPECTED RESULT:

预期结果:

Id   | RunId | Value | EnvironmentId
---
4    | 2     | 40    | 3
5    | 2     | 50    | 3
6    | 3     | 60    | 1
8    | 5     | 80    | 4
9    | 6     | 90    | 2

So only the rows with the most recent/highest RunId from the RunTable per EnvironmentId. For example, for the EnvironmentId of "1", I only want rows that contain a RunId of "3" because the most recent RunId on EnvironmentId "1" from the RunTable is "3". Likewise, the most recent run for EnvironementId "2" was RunId "6"

因此，只有最近/最高RunId的行才能符合每个环境的运行情况。例如，对于“1”的环境，我只想要包含“3”的RunId的行，因为最新的关于“1”的RunId是“3”。同样，最近一次出现的“2”是“6”

2 个解决方案

#1

Use a subquery to get the max runid for each environmentid from the runtable. Join the obtained result to the issuetable and select the required columns.

使用子查询从runtable获取每个环境的最大runid。将获得的结果连接到可发布的并选择所需的列。

select i.id, i.runid, i.value, r.environmentid
from (select environmentid, max(runid) maxrunid
      from runtable 
      group by environmentid) r
join issuetable i on i.runid = r.maxrunid
order by i.runid, i.id

#2

These days one can use the analytical functions like RANK, DENSE_RANK, ROW_NUMBER to generate some ranking of your records.

现在人们可以使用RANK、DENSE_RANK、ROW_NUMBER等分析函数来生成一些记录的排序。

Window functions are part of the ANSI SQL:2003 standard.
And I've at least encountered them on TeraData, Oracle and SQL-Server.

窗口函数是ANSI SQL:2003标准的一部分。我至少在TeraData, Oracle和SQL-Server中遇到过它们。

select Id, RunId, Value, EnvironmentId
from (
  select i.*, r.EnvironmentId,
  dense_rank() over (partition by r.EnvironmentId order by r.RunId desc) as RN
  from issuetable i
  inner join runtable r on (i.RunId = r.RunId)
) Q
where RN = 1
order by Id;

The inner query would yield the following results :

内部查询将产生以下结果:

Id  RunId   Value   EnvironmentId   RN
1   1       10      1               2
2   1       20      1               2
3   1       30      1               2
4   2       40      3               1
5   2       50      3               1
6   3       60      1               1
7   4       70      2               2
8   5       80      4               1
9   6       90      2               1

#1