blocks|key|1836544|text|您需要从查询中删除col1和col2，因为它们使行是惟一的。试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1836545|SELECT+user,+max(col3)+AS+col3+FROM+table+GROUP+BY+user+ORDER+BY+user;|code-block|syntax|javascript|1836546|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You need to eliminate the col1 and col2 from the query, since they make the rows unique. Try 

<pre><code>SELECT user, max(col3) AS col3 FROM table GROUP BY user ORDER BY user;
</code></pre>

blocks|key|3290802|text|在查询中，为每行选择最大值，并将其选作列。您希望知道最大值是多少，并将其用作选择具有该值的行的限制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3290803|3290804|假设您想要来自col3的max，您可以这样做：|offset|length|style|CODE|3290805|select+t.user,
+++++++t.col1,
+++++++t.col2,
+++++++t.col3
from+tablename+t
join+(
++++++select+max(col3)+as+'maximum'
++++++from+tablename
+++++)+aux+on+aux.maximum+=+t.col3|code-block|syntax|javascript|3290806|aux连接选择col3的max值，然后使用col3作为限制与表连接。|3290807|entityMap^0|0|0|7|4|C|3|0|0|0|3|7|4|C|3|L|4|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|C|3|D|5|6|7|T|8|@$E|U|F|V|G|H]|$E|W|F|X|G|H]]|9|@]|A|$]]|$1|I|3|J|5|K|7|Y|8|@]|9|@]|A|$L|M]]|$1|N|3|O|5|6|7|Z|8|@$E|10|F|11|G|H]|$E|12|F|13|G|H]|$E|14|F|15|G|H]|$E|16|F|17|G|H]]|9|@]|A|$]]|$1|P|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|Q|$]]

In your query, you select the max for each row and that is selected as a column.
You want to know what's the max and use that as a restriction to select the rows with that value.

<hr>

Assuming you want the <code>max</code> from <code>col3</code>, you can do it like this:

<pre><code>select t.user,
 t.col1,
 t.col2,
 t.col3
from tablename t
join (
 select max(col3) as 'maximum'
 from tablename
 ) aux on aux.maximum = t.col3
</code></pre>

The <code>aux</code> join selects the <code>max</code> value of <code>col3</code> and then you join with your table using <code>col3</code> as the restriction.

blocks|key|3202450|text|其他数据库(例如MS+SQL+Server)不允许您将聚合值与非聚合值混合在一起，因为这样会得到错误的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3202451|因此，如果您希望从最大值所在的记录中获得非聚合值，请再次对该表进行连接：|3202452|select+x.user,+y.col1,+y.col2,+x.col3
from+(
++select+user,+max(col3)+as+col3
++from+table
++group+by+user
)+x
inner+join+table+y+on+y.user+=+x.user+and+y.col3+=+x.col3
order+by+x.user|code-block|syntax|javascript|3202453|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Other databases (e.g. MS SQL Server) doesn't let you mix aggergated values with non-aggregated values, just because you would get the wrong result.

So, if you want non-aggregated values from the record where the maximum value was, join against the table again:

<pre><code>select x.user, y.col1, y.col2, x.col3
from (
 select user, max(col3) as col3
 from table
 group by user
) x
inner join table y on y.user = x.user and y.col3 = x.col3
order by x.user
</code></pre>

blocks|key|1565806|text|这可能看起来很疯狂，但它应该对你有效|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1565807|SELECT+B.*+FROM
(
++++SELECT+user,MAX(col3)+col3
++++FROM+mytable+GROUP+BY+user
)+A
INNER+JOIN+mytable+B
USING+(user,col3)+ORDER+BY+user,col3;|code-block|syntax|javascript|1565808|以下是示例数据：|1565809|mysql>+DROP+DATABASE+IF+EXISTS+terry;
Query+OK,+1+row+affected+(0.06+sec)

mysql>+CREATE+DATABASE+terry;
Query+OK,+1+row+affected+(0.00+sec)

mysql>+USE+terry
Database+changed
mysql>+CREATE+TABLE+mytable
++++->+(user+INT,col1+INT,col2+INT,col3+int,
++++->+key+(user,col3));
Query+OK,+0+rows+affected+(0.06+sec)

mysql>+INSERT+INTO+mytable+VALUES
++++->+(1,++++1,++++2,++++3),(1,++++3,++++4,++++5),
++++->+(2,++++2,++++3,++++1),(3,++++1,++++1,++++3),
++++->+(3,++++2,++++4,++++6),(4,++++5,++++1,++++5);
Query+OK,+6+rows+affected+(0.07+sec)
Records:+6++Duplicates:+0++Warnings:+0

mysql>+SELECT+*+FROM+mytable;
%2B------%2B------%2B------%2B------%2B
%7C+user+%7C+col1+%7C+col2+%7C+col3+%7C
%2B------%2B------%2B------%2B------%2B
%7C++++1+%7C++++1+%7C++++2+%7C++++3+%7C
%7C++++1+%7C++++3+%7C++++4+%7C++++5+%7C
%7C++++2+%7C++++2+%7C++++3+%7C++++1+%7C
%7C++++3+%7C++++1+%7C++++1+%7C++++3+%7C
%7C++++3+%7C++++2+%7C++++4+%7C++++6+%7C
%7C++++4+%7C++++5+%7C++++1+%7C++++5+%7C
%2B------%2B------%2B------%2B------%2B
6+rows+in+set+(0.00+sec)|1565810|以下是查询的输出：|1565811|mysql>+SELECT+B.*+FROM
++++->+(
++++->+++++SELECT+user,MAX(col3)+col3
++++->+++++FROM+mytable+GROUP+BY+user
++++->+)+A
++++->+INNER+JOIN+mytable+B
++++->+USING+(user,col3)+ORDER+BY+user,col3;
%2B------%2B------%2B------%2B------%2B
%7C+user+%7C+col1+%7C+col2+%7C+col3+%7C
%2B------%2B------%2B------%2B------%2B
%7C++++1+%7C++++3+%7C++++4+%7C++++5+%7C
%7C++++2+%7C++++2+%7C++++3+%7C++++1+%7C
%7C++++3+%7C++++2+%7C++++4+%7C++++6+%7C
%7C++++4+%7C++++5+%7C++++1+%7C++++5+%7C
%2B------%2B------%2B------%2B------%2B
4+rows+in+set+(0.02+sec)

mysql>|1565812|此输出将是正确的，因为对于您给出的示例数据中的每个用户，col3的最大值只出现一次。如果给定用户的两行具有与最大值相同的col3，则这两行都应该出现。|1565813|为了说明这一点，让我们使用user=3和col3=6添加另一行；|1565814|mysql>+INSERT+INTO+mytable+VALUES+(3,8,9,6);
Query+OK,+1+row+affected+(0.10+sec)

mysql>+SELECT+B.*+FROM
++++->+(
++++->+++++SELECT+user,MAX(col3)+col3
++++->+++++FROM+mytable+GROUP+BY+user
++++->+)+A
++++->+INNER+JOIN+mytable+B
++++->+USING+(user,col3)+ORDER+BY+user,col3;
%2B------%2B------%2B------%2B------%2B
%7C+user+%7C+col1+%7C+col2+%7C+col3+%7C
%2B------%2B------%2B------%2B------%2B
%7C++++1+%7C++++3+%7C++++4+%7C++++5+%7C
%7C++++2+%7C++++2+%7C++++3+%7C++++1+%7C
%7C++++3+%7C++++2+%7C++++4+%7C++++6+%7C
%7C++++3+%7C++++8+%7C++++9+%7C++++6+%7C
%7C++++4+%7C++++5+%7C++++1+%7C++++5+%7C
%2B------%2B------%2B------%2B------%2B
5+rows+in+set+(0.00+sec)

mysql>|1565815|试一试吧！|1565816|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

This may see crazy but it should work for you

<pre><code>SELECT B.* FROM
(
 SELECT user,MAX(col3) col3
 FROM mytable GROUP BY user
) A
INNER JOIN mytable B
USING (user,col3) ORDER BY user,col3;
</code></pre>

Here is the sample data:

<pre><code>mysql&gt; DROP DATABASE IF EXISTS terry;
Query OK, 1 row affected (0.06 sec)

mysql&gt; CREATE DATABASE terry;
Query OK, 1 row affected (0.00 sec)

mysql&gt; USE terry
Database changed
mysql&gt; CREATE TABLE mytable
 -&gt; (user INT,col1 INT,col2 INT,col3 int,
 -&gt; key (user,col3));
Query OK, 0 rows affected (0.06 sec)

mysql&gt; INSERT INTO mytable VALUES
 -&gt; (1, 1, 2, 3),(1, 3, 4, 5),
 -&gt; (2, 2, 3, 1),(3, 1, 1, 3),
 -&gt; (3, 2, 4, 6),(4, 5, 1, 5);
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql&gt; SELECT * FROM mytable;
+------+------+------+------+
| user | col1 | col2 | col3 |
+------+------+------+------+
| 1 | 1 | 2 | 3 |
| 1 | 3 | 4 | 5 |
| 2 | 2 | 3 | 1 |
| 3 | 1 | 1 | 3 |
| 3 | 2 | 4 | 6 |
| 4 | 5 | 1 | 5 |
+------+------+------+------+
6 rows in set (0.00 sec)
</code></pre>

Here is the output of the query:

<pre><code>mysql&gt; SELECT B.* FROM
 -&gt; (
 -&gt; SELECT user,MAX(col3) col3
 -&gt; FROM mytable GROUP BY user
 -&gt; ) A
 -&gt; INNER JOIN mytable B
 -&gt; USING (user,col3) ORDER BY user,col3;
+------+------+------+------+
| user | col1 | col2 | col3 |
+------+------+------+------+
| 1 | 3 | 4 | 5 |
| 2 | 2 | 3 | 1 |
| 3 | 2 | 4 | 6 |
| 4 | 5 | 1 | 5 |
+------+------+------+------+
4 rows in set (0.02 sec)

mysql&gt;
</code></pre>

This output would be correct because for every user in the sample data you gave, there is only one occurrence of a max value for col3. If two rows for a give user had the same col3 as a max value, they both should appear.

To illustrate this, let's add another row with user=3 and col3=6;

<pre><code>mysql&gt; INSERT INTO mytable VALUES (3,8,9,6);
Query OK, 1 row affected (0.10 sec)

mysql&gt; SELECT B.* FROM
 -&gt; (
 -&gt; SELECT user,MAX(col3) col3
 -&gt; FROM mytable GROUP BY user
 -&gt; ) A
 -&gt; INNER JOIN mytable B
 -&gt; USING (user,col3) ORDER BY user,col3;
+------+------+------+------+
| user | col1 | col2 | col3 |
+------+------+------+------+
| 1 | 3 | 4 | 5 |
| 2 | 2 | 3 | 1 |
| 3 | 2 | 4 | 6 |
| 3 | 8 | 9 | 6 |
| 4 | 5 | 1 | 5 |
+------+------+------+------+
5 rows in set (0.00 sec)

mysql&gt;
</code></pre>

Give it a Try !!!

blocks|key|3202570|text|select+t1.user,+t1.col1,+t1.col2,+t1.col3
from+table1+t1
where+not+exists(select+*+from+table1+t2
+++++++++++++++++where+t1.user+=+t2.user
+++++++++++++++++++and+t1.col3+<+t2.col3)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3202571|如果一个用户有几行具有相同(最大)+col3值，则将返回该用户具有该值的所有行。|unstyled|3202572|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select t1.user, t1.col1, t1.col2, t1.col3
from table1 t1
where not exists(select * from table1 t2
 where t1.user = t2.user
 and t1.col3 &lt; t2.col3)
</code></pre>

If there are several rows for one user with the same (maximum) col3 value, then all rows with this value for this user will be returned.

Whenever I use the <code>max</code> function I somehow lose all the connection to my other values, so that the row that is printed later doesn't correlate with the column that I ran max on anymore.

So my table is:

<pre><code>user col1 col2 col3
1 1 2 3
1 3 4 5
2 2 3 1
3 1 1 3
3 2 4 6
4 5 1 5
</code></pre>

So if I run

<pre><code>select user, col1, col2, max(col3) as col3
from table
group by user
order by user;
</code></pre>

I would get

<pre><code>user col1 col2 col3
1 1 2 5
2 2 3 1
3 1 1 6
4 5 1 5
</code></pre>

So the max value of col3 is correct, but it doesn't get the correct row of that value.

What I want is to get the max value of a column and return that row for each user. If there are multiple max values that it should return all users, even if it has same user id.

Get row with max(column)

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

每当我使用max函数时，不知何故我就失去了与其他值的所有连接，因此稍后打印的行将不再与我运行max的列相关联。所以我的表格是：user col1 col2 col31    1    2    31    3    4    52    2    3    13    1    1    33    2    4   ...

问获取具有最大值的行(列)
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取具有最大值的行(列)EN