SQL - 如何选择具有最大值列的行

Question

date                 value

18/5/2010, 1 pm        40
18/5/2010, 2 pm        20
18/5/2010, 3 pm        60
18/5/2010, 4 pm        30
18/5/2010, 5 pm        60
18/5/2010, 6 pm        25 

我需要查询具有最大值（即60）的行。因此，我们得到了两行结果。我需要在那一天中时间戳最低的那一行（即2010年5月18日下午3点-> 60）。

- Abhishek

---

谢谢大家。现在如果日期跨度为10天，我需要针对每一天进行查询，结果为10行，每行具有该特定日期的最大值。请协助我解决这个问题。 - Abhishek

Answer 1

类似TOP、LIMIT、ROWNUM等关键词是与数据库相关的。请阅读本文获取更多信息。

http://en.wikipedia.org/wiki/Select_(SQL)#Result_limits

Oracle：可以使用ROWNUM。

select * from (select * from table 
order by value desc, date_column) 
where rownum = 1;

更具体地回答这个问题：

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc)
where rownum <= 1

- Sujee

---

我建议使用ROWNUM查询，Oracle可以对此类查询进行很好的优化（即使需要排序，它也不会对整个表进行排序 - 它只是在扫描表时保留最顶部的行），并且通过适当的索引，甚至不需要执行排序。 - Jeffrey Kemp

非常好的答案。不过，我更喜欢使用LIMIT :) - Marco Sulla

Answer 2

分析！这避免了两次访问表：

SELECT DISTINCT
       FIRST_VALUE(date_col)  OVER (ORDER BY value_col DESC, date_col ASC),
       FIRST_VALUE(value_col) OVER (ORDER BY value_col DESC, date_col ASC)
FROM   mytable;

- Jeffrey Kemp

---

从纯教育角度来看，我很想看一下这种性能差异的情况。根据我在Toms asktom.oracle网站上了解到的信息，使用FIRST_VALUE会有相当大的开销。请问您是否能够为我指引一些比较它们性能差异的结果呢？ - TerrorAustralis

如果您在（value_col，date_col）上有索引，则会发现Oracle使用Sujee的查询非常好，因为它将使用“COUNT STOPKEY”优化。 - Jeffrey Kemp

它避免了两次扫描表，但它为每一行计算first_value列，然后distinct丢弃所有但一个。比大多数其他答案好，但在这里聚合是正确的方法。 - Rob van Wijk

是的，Sujee比我更快地提出了ROWNUM解决方案，这个方案更好 :) - Jeffrey Kemp

Answer 3

答案是添加一个子查询：

SELECT [columns]
FROM table t1
WHERE value= (select max(value) from table)
AND date = (select MIN(date) from table t2 where t1.value = t2.value)

这应该可以工作，并且消除了在日期子句中需要额外的子选择的必要性。

- TerrorAustralis

---

HAVING子句中的任何非聚合函数都需要是GROUP BY子句的一部分。 - Gary Myers

很好的观点。我在Sybase上测试了我的代码，它让你这样做。我修改了我的答案以适应所需的数据库。 - TerrorAustralis

嗯，你仍然有一个子查询 - 实际上，有两个。 - Jeffrey Kemp

感谢您的反馈，是的有两种方法，但标准替代方法是执行 AND date = select min(date) from table where value = (select max(value) from table))。我引用 J Brooks 的答案（比这个高3个）作为证明。通过引用先前子查询已获取的值，可以节省1个嵌套级别。 - TerrorAustralis

啊，我现在明白你在说什么了。当然，所有的子查询都是不必要的 :) - Jeffrey Kemp

这可能不会返回任何行，因为你要求的是整体的最大值和整体的最小日期在同一行...很有可能这种情况不存在，所以它将不会返回任何行。 - Chris Schaller

Answer 4

SQL> create table t (mydate,value)
  2  as
  3  select to_date('18/5/2010, 1 pm','dd/mm/yyyy, hh am'), 40 from dual union all
  4  select to_date('18/5/2010, 2 pm','dd/mm/yyyy, hh am'), 20 from dual union all
  5  select to_date('18/5/2010, 3 pm','dd/mm/yyyy, hh am'), 60 from dual union all
  6  select to_date('18/5/2010, 4 pm','dd/mm/yyyy, hh am'), 30 from dual union all
  7  select to_date('18/5/2010, 5 pm','dd/mm/yyyy, hh am'), 60 from dual union all
  8  select to_date('18/5/2010, 6 pm','dd/mm/yyyy, hh am'), 25 from dual
  9  /

Table created.

SQL> select min(mydate) keep (dense_rank last order by value) mydate
  2       , max(value) value
  3    from t
  4  /

MYDATE                   VALUE
------------------- ----------
18-05-2010 15:00:00         60

1 row selected.

敬礼， 罗布。

- Rob van Wijk

---

很好，但是Oracle仍然会进行全表扫描 - 不幸的是，它无法应用COUNT STOPKEY优化。 - Jeffrey Kemp

正确。这是合乎逻辑的，因为要确定某个值是否为最大值，你需要访问所有值，或者像在索引中一样对它们进行预排序。 - Rob van Wijk

Answer 5

从技术上讲，这与@Sujee的答案相同。它还取决于您使用的Oracle版本是否可行。(我认为此语法是在Oracle 12中引入的??)

SELECT *
FROM   table
ORDER BY value DESC, date_column ASC
FETCH  first 1 rows only;

就像我所说的，如果你看一下代码背后的原理，我认为这段代码会被Oracle Optimizer内部解包，然后像@Sujee的那样读取。然而，我喜欢漂亮的代码，没有充分理由的嵌套select语句并不算美观!! :-P

- cartbeforehorse

Answer 6

在Oracle中：

根据范围获取表中最大(high_val)的键。

select high_val, my_key
from (select high_val, my_key
      from mytable
      where something = 'avalue'
      order by high_val desc)
where rownum <= 1

- Eric Leschinski

Answer 7

在Oracle数据库中：

create table temp_test1 (id number, value number, description varchar2(20));

insert into temp_test1 values(1, 22, 'qq');
insert into temp_test1 values(2, 22, 'qq');
insert into temp_test1 values(3, 22, 'qq');
insert into temp_test1 values(4, 23, 'qq1');
insert into temp_test1 values(5, 23, 'qq1');
insert into temp_test1 values(6, 23, 'qq1');

SELECT MAX(id), value, description FROM temp_test1 GROUP BY value, description;

Result:
    MAX(ID) VALUE DESCRIPTION
    -------------------------
    6         23    qq1
    3         22    qq

- shrinath

Answer 8

最简单的回答是：

--设置一个名为“t1”的测试表

create table t1
(date datetime,
value int)

-- 加载数据。 -- 注意：日期格式与问题中不同

insert into t1
Select '5/18/2010 13:00',40
union all
Select '5/18/2010 14:00',20
union all
Select '5/18/2010 15:00',60 
union all
Select '5/18/2010 16:00',30 
union all
Select '5/18/2010 17:00',60 
union all
Select '5/18/2010 18:00',25 

-- 查找数量最大和日期最小的行。

select *
from t1
where value = 
    (select max(value)  from t1)
and date = 
    (select min(date) 
    from t1
    where value = (select max(value)  from t1))

我知道你可以做出“TOP 1”的答案，但通常你的解决方案会变得足够复杂，以至于出于某种原因你无法使用它。

- JBrooks

---

这种要求在Oracle中对同一张表进行三次额外的访问是没有必要的。 - Jeffrey Kemp

额外的访问权限？这将与其他一些看起来做得更少但不易读的执行计划相同。而且我知道Oracle没有TOP命令。 - JBrooks

Answer 9

你可以使用这个函数，ORACLE DB

 public string getMaximumSequenceOfUser(string columnName, string tableName, string username)
    {
        string result = "";
        var query = string.Format("Select MAX ({0})from {1} where CREATED_BY = {2}", columnName, tableName, username.ToLower());

        OracleConnection conn = new OracleConnection(_context.Database.Connection.ConnectionString);
        OracleCommand cmd = new OracleCommand(query, conn);
        try
        {
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();
            dr.Read();
            result = dr[0].ToString();
            dr.Dispose();
        }
        finally
        {
            conn.Close();
        }
        return result;
    }

- Nada N. Hantouli