数据库分组查询最大值的问题

图a

 

图b

 

需求：从transResTimeNum中按照transName分组找到各自的最大值对应的runningTime

 

解决办法：

1、先分组查询找到不同transName对应的最大值；

2、然后根据最大值找到对应的runningTime

 

Sql语句如下：

先分组查询找到不同transName对应的最大值

select MAX(transResTimeNum),transName from ps_transresponse GROUP BY transName

查询结果如下：

 

 

第一次尝试：

select MAX(transResTimeNum),transName，runningTime from ps_transresponse GROUP BY transName

 

查询结果不正确：

 

不正确原因:因为group by transName，所以在transName上，runningTime是直接跟着第一次出现transName的runingTime。

 

 

第二次尝试

 

select runningTime,transName,transResTimeNum from ps_transresponse

where transResTimeNum in (SELECT MAX(transResTimeNum) from ps_transresponse GROUP BY transName) and runId = 2  GROUP BY transName

 

查询结果正确

 

查询结果没有问题，查询时间也没有问题。

 

sql大法升级版

上面的sql语句中出现了关键词in，在小表查询中是完全没有问题的，但是在百万级的数据面前，上面的sql语句查询问题很大，一开始在我在查询的时候要花很长很长的时间，于是我explain这一句，发现了很严重的问题。

 

Explain结果：

 

 

第三次尝试

 

select t1.runningTime, t1.transName,t1.transResTimeNum from ps_transresponse t1,(select transName,Max(transResTimeNum)as transResTimeNum from ps_transresponse  where runId = 0 GROUP BY transName) t2 where t1.transName=t2.transName and t1.transResTimeNum =t2.transResTimeNum and runId =0 group by transName

 

查询结果：

 

 

这是将最大值作为一张表，和原始表进行查询，这样我又想到了使用join，于是我进行了

 

第四次尝试。

 

select t1.runningTime,t1.transName,t1.transResTimeNum from ps_transresponse t1 JOIN (select transName,Max(transResTimeNum)as transResTimeNum from ps_transresponse  where runId = 0 GROUP BY transName) t2 on t1.transName=t2.transName and t1.transResTimeNum =t2.transResTimeNum  GROUP BY transName

 

查询结果：

GROUP BY 子句将表按列的值分组，列的值相同的分在一组。如果GROUP BY后有多个列名，则先按第一列名分组，再按第二列名在组中分组，原则上可以一直分下去，直到在所有基本组中，GROUP BY子句所指定的列都具有相同的值，HAVING后的条件是选择基本组的条件。GROUP BY子句常与聚集函数联用，此时聚集函数以基本组为计算对象。加了 GROUP BY子句后，SELECT子句所取的值必须在基本组中是唯一的，即只能是GROUP BY子句所指明的列或聚集函数。若无GROUP BY子句，则聚集函数以整个表为计算对象，此时SELECT子句只能取聚集函数，而不能取某一列。

 

王能斌，《数据库系统教程》（第二版），3.4.3。