如何在SQL中以MAX(Column value)，DISTINCT by MULTIPLE columns来选择行

问题描述

我的桌子是:

id  env       date      name    PI # 
---|-----|------------|--------|-----
 1 | 10  | 04/03/2009 |   john | 399 
 2 | 11  | 04/03/2009 |  juliet| 244 
 5 | 12  | 04/03/2009 |  borat | 345
 3 | 10  | 03/03/2009 |  john  | 399
 4 | 11  | 03/03/2009 | juliet | 244
 6 | 12  | 03/03/2009 |  borat | 500
 7 | 13  | 24/12/2008 |  borat | 650
 8 | 13  | 01/01/2009 |  borat | 650

此帖子对以下问题进行了稍微的修改.

如何可以选择带有MAX(Column value)的行，通过SQL中的另一列来DISTINCT?

区别在于，我想选择每个不同的环境，并选择保存日期最大值的PI#.例如，当两行具有相同的env&它们的PI编号相同(第3& 1，2& 4，4，7&8行)，我想返回具有最大日期的行.

下面是期望的结果.

id  env     date      name    PI # 
---|----|------------|--------|----
 1 | 10 | 04/03/2009 | john   | 399
 2 | 11 | 04/03/2009 | juliet | 244
 5 | 12 | 04/03/2009 | borat  | 345
 6 | 12 | 03/03/2009 | borat  | 500
 8 | 13 | 01/01/2009 | borat  | 650

解决方案

典型方法使用相关子查询:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.env = t.env);

也许更好的方法是:

select t.*
from t
where t.id = (select t2.id
              from t t2 
              where t2.env = t.env
              order by t2.date desc, t2.id desc
              limit 1
             );

这会稍微好一点，因为(1)id可能是主键，因此匹配速度更快； (2)如果同一日期有多行，则仅返回一行.

My table is:

id  env       date      name    PI # 
---|-----|------------|--------|-----
 1 | 10  | 04/03/2009 |   john | 399 
 2 | 11  | 04/03/2009 |  juliet| 244 
 5 | 12  | 04/03/2009 |  borat | 345
 3 | 10  | 03/03/2009 |  john  | 399
 4 | 11  | 03/03/2009 | juliet | 244
 6 | 12  | 03/03/2009 |  borat | 500
 7 | 13  | 24/12/2008 |  borat | 650
 8 | 13  | 01/01/2009 |  borat | 650

This post is slightly modified from the question below.

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

The difference is that I want to select each distinct environment and PI# holding the maximum value of date. For example when two rows have the same env & their PI #s are the same (rows 3 & 1 ,2 & 4, 7 & 8), I would like to return the row with the max date.

Below is the desired result.

id  env     date      name    PI # 
---|----|------------|--------|----
 1 | 10 | 04/03/2009 | john   | 399
 2 | 11 | 04/03/2009 | juliet | 244
 5 | 12 | 04/03/2009 | borat  | 345
 6 | 12 | 03/03/2009 | borat  | 500
 8 | 13 | 01/01/2009 | borat  | 650

解决方案

A typical method uses a correlated subquery:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.env = t.env);

Perhaps a slightly better method is:

select t.*
from t
where t.id = (select t2.id
              from t t2 
              where t2.env = t.env
              order by t2.date desc, t2.id desc
              limit 1
             );

This is slightly better because (1) id is probably a primary key, so the match is faster; and (2) if there are multiple rows on the same date, then only one row is returned.

这篇关于如何在SQL中以MAX(Column value)，DISTINCT by MULTIPLE columns来选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！