如何查询具有最低值的行，又如何知道最高值的值? [英] How to query row with lowest value, and also to know the value of the highest value?

问题描述

考虑以下两个查询:

SELECT *, 'b'    AS b    FROM someTable ORDER BY a ASC LIMIT 1;
SELECT *, MAX(a) AS maxA FROM someTable ORDER BY a ASC LIMIT 1;

如前所述，前一个查询返回具有最低值a的行.后一个查询返回存储在磁盘上的第一行(通常是主键值最低的行). 我该如何解决?我的目的是获得具有最低a值的列的整行(如果有多个，我只需要一个即可，这无关紧要) ，此外，我确实需要最高年龄的价值观.在理想情况下，我将运行两个查询，但是由于对象在此应用程序中的序列化方式，如果不重构很多不是我的代码，我将无法做到这一点.我实际上不介意MySQL引擎本身是否必须查询两次，重要的一点是输出必须在一行中返回.不幸的是，我无法为此查询编写存储过程.是的，*运算符很重要，我无法列出所需的字段.而且有太多的行无法全部归还！

The former query returns the row with the lowest value of a, as expected. The latter query returns the first row stored on disk (usually the row with the lowest value for primary key). How can I work around this? My intention is to get the full row of the column with the lowest a value (if there is more than one I only need one, it does not matter which), and additionally I do need the value of the highest age. In a perfect world I would run two queries, but due to the way that objects are serialised in this application I cannot do that without refactoring a lot of code that isn't mine. I actually don't mind if the MySQL engine itself must query twice, the important bit is that the output be returned in a single row. I cannot write a stored procedure for this query, unfortunately. And yes, the * operator is important, I cannot list the needed fields. And there are too many row to return them all!

请注意，此问题与

Note that this question is superficially similar to a previous question, however the question asked there was ill-formed and ambiguous, therefore all the answers addressed the issue that was not my intention (however useful, I did learn much and I'm happy that it turned out that way). This question asks the intended question more clearly and so should attract different answers.

推荐答案

为什么不只运行它:

SELECT MIN(a) as minA, MAX(a) AS maxA FROM someTable

不幸的是，MySQL不知道窗口函数.因此，如果您真的想选择*以及最小值/最大值，我想您将不得不采用JOIN:

Unfortunately, MySQL doesn't know window functions. So if you really want to select * along with min/max values, I guess you'll have to resort to a JOIN:

SELECT * FROM 
(
  SELECT * FROM someTable ORDER BY a ASC LIMIT 1
) t1
CROSS JOIN
(
  SELECT MIN(a) as minA, MAX(a) AS maxA FROM someTable
) t2

或转到子选择，如 Imre L的答案

这篇关于如何查询具有最低值的行，又如何知道最高值的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！