mysql - 如何在 Mysql 中使用 where 子句中的 Max counted id 作为参数

Question

这里是返回 TraineeID 的第一个查询，它按计数取最大值。我需要在第二个查询中使用此 TraineeID 作为 where 子句中的参数。有什么办法可以做到这一点。提前谢谢你。

查询 1:

SELECT TraineeID, COUNT(TraineeID) as maxfinger 
FROM tbl_raw_attendance WHERE date(CreatedTime)='2016-04-13' 
GROUP by TraineeID ORDER by maxfinger DESC
LIMIT 1 

查询 2:

SELECT @n := @n + 1 RowNumber, t.*
FROM (select @n:=0) initvars, tbl_raw_attendance t
WHERE date(t.CreatedTime)='2016-04-13'

Answer 1

您可以使用子查询来完成:

SELECT @n := @n + 1 RowNumber, t.*
FROM (select @n:=0) initvars, tbl_raw_attendance t
WHERE date(t.CreatedTime)='2016-04-13'
   AND t.TraineeID = (SELECT TraineeID FROM tbl_raw_attendance
                      WHERE date(CreatedTime)='2016-04-13' 
                      GROUP by TraineeID ORDER by COUNT(TraineeID) DESC
                      LIMIT 1 )