SQL查询“太"了long - 选择不同和最大 [英] SQL query taking "too" long - select with distinct and max

问题描述

我有一个场景:

一个带有 POSITION 和每个位置关联值的表，但我总是有 4 个值用于同一个位置，所以我的表的一个例子是:

A table with POSITION and a value associated for each position, BUT I have always 4 values for the same position, so an example of my table is:

position | x| values 
1 | x1 | 0
1 | x2 | 1
1 | x3 | 1.4
1 | x4 | 2
2 | x1 | 3
2 | x2 | 10
2 | x3 | 12.4
2 | x4 | 22

我需要一个查询来返回每个唯一位置值的 MAX 值.现在，我正在查询它:

I need a query that returns me the MAX value for each unique position value. Now, I am querying it with:

SELECT DISTINCT (position) AS p, (SELECT MAX(values) AS v FROM MYTABLE  WHERE position = p) FROM MYTABLE;

我花了 1651 行(39.93 秒)，1651 行只是对这个数据库的一个测试(它可能应该有超过 1651 行.

It took me 1651 rows in set (39.93 sec), and 1651 rows is just a test for this database (it probably should have more then 1651 rows.

我做错了什么?有没有更好的方法可以更快地获得它?

What am I doing wrong ? are there any better way to get it in a faster way ?

感谢任何帮助.干杯.，

Any help is appreciated. Cheers.,

推荐答案

使用 GroupBy-Clause:

Use the GroupBy-Clause:

SELECT Position, MAX(VALUES) FROM TableName
GROUP BY Position

另外，看看文档(关于 groupby):http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Also, have a look at the documentation (about groupby): http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

这篇关于SQL查询“太"了long - 选择不同和最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！