mysql - 选择 MySQL 数据库中单列最大值的行

Question

我有一个名为 NetLogs 的 MySQL 表

servername   , session , label     , occurence
| Nost       |   11973 | Unknown   |   1 |
| Nost       |   11976 | Scan      |  74 |
| Nost       |   11976 | Unknown   |  35 |
| Gold       |   11998 | Attack    |   1 |

我需要得到

Nost | 11973|unknown|1|
Nost| 11976 |Scan | 74|
Gold|11998|Attack|1|

结果。

我试过:

select t1.* from NetLogs t1 left join NetLogs t2 
on t1.servername=t2.servername and t1.session=t2.session and t1.occurence < t2.occurence 
where t2.occurence is null;

但我收到错误 1137 - 无法重新打开表格。

如果最后没有出现次数，我也会满足于相同的结果。

所以下面的一些解决方案对我不起作用，所以我将表重新制作为常规表而不是临时表并且它们起作用了，这让我觉得我可能曾经有过一个有效的查询，但是出现错误是因为我在临时表上运行它...

这正是我想要的效果:

select a.* from NetLogs a where a.occurence = ( Select max(occurence) occurence from NetLogs b where a.session = b.session and a.serverName = b.serverName);

Answer 1

SELECT  a.*
FROM    NetLogs a
        INNER JOIN
        (
            SELECT  session, MAX(occurence) occurence
            FROM    NetLogs
            GROUP   BY session
        ) b ON a.session = b.session AND
                a.occurence = b.occurence

• SQLFiddle Demo

另一种方式，

SELECT  a.*
FROM    NetLogs a
WHERE   a.occurence = 
        (
            SELECT  MAX(occurence) occurence
            FROM    NetLogs b
            WHERE   a.session = b.session
        ) 

• SQLFiddle Demo