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

Question

我很新手SQL，这个问题让我困惑了。你能帮我解决这个查询吗？

我有以下两个表：

表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 

表2：RunTable

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

我需要从RunTable中按EnvironmentId分组，表示最大RunId的IssueTable行。 我需要从表中得到的结果是：

期望结果：

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

所以，只有每个EnvironmentId从RunTable中具有最新/最高的RunId的行。例如，对于EnvironmentId为“1”，我只想要包含RunId为“3”的行，因为EnvironmentId“1”在RunTable中的最新RunId是“3”。同样，EnvironementId“2”的最近运行是RunId“6”。

- Lostinthegame

---

你使用的关系型数据库是什么？SQL Server、PostgreSQL还是Oracle？ - Juan Carlos Oropeza

Answer 1

使用子查询从runtable中获取每个environmentid的最大runid。将所得结果与issuetable连接，并选择所需列。

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

- Vamsi Prabhala

Answer 2

现在，我们可以使用诸如RANK、DENSE_RANK和ROW_NUMBER等分析函数来生成记录的排名。

窗口函数是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;

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

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

- LukStorms

---

窗口函数是在SQL:2003中引入的，而不是1999年。DB2、Postgres、Firebird等几个数据库管理系统也支持它们。 - user330315

确实，我找到的那篇文章让我错误地假设了这些函数的错误标准。感谢您指出。已在答案中进行了更正。 - LukStorms

值得注意的是，ROW_NUMBER() 是建议方法中唯一确保每个组（分区）仅有一个行即使存在多个最大值的方法 - 同时也方便在 ORDER BY 子句中使用其他字段作为 tie-breaker。 - Dana