1  

SELECT  a.*, b.numOfTakingExams
FROM    employeeExam a
        INNER JOIN
        (
            SELECT  employee_id,
                    MAX(date) max_Date,
                    COUNT(*) numOfTakingExams 
            FROM    employeeExam
            GROUP BY course_ID, employee_id
        ) b ON  a.employee_id = b.employee_id AND
                a.date = b.max_Date

• SQLFiddle Demo

you can also get the latest record by the maximum ID if it is set as AUTO_INCREMENT, this query below yields the same result from the query above,

如果将最大ID设置为AUTO_INCREMENT,您也可以通过最大ID获取最新记录,此查询会产生与上述查询相同的结果,

SELECT  a.*, b.numOfTakingExams
FROM    employeeExam a
        INNER JOIN
        (
            SELECT  employee_id,
                    MAX(id) max_Date,
                    COUNT(*) numOfTakingExams 
            FROM    employeeExam
            GROUP BY course_ID, employee_id
        ) b ON  a.employee_id = b.employee_id AND
                a.id = b.max_Date

• SQLFiddle Demo

1  

Try this query -

试试这个查询 -

SELECT
  t1.id, t1.course_id, t1.employee_id, t1.degree, t1.date, t2.numOfTakingExams
FROM
  mployeexam t1
  JOIN (
        SELECT employee_id, MAX(date) date, COUNT(*) numOfTakingExams
        FROM mployeexam
        GROUP BY employee_id
    ) t2
    ON t1.employee_id = t2.employee_id AND t1.date = t2.date

1  

Have you tried a join with itself? In the first you select on the IDs containing the "last exams" and in the second you join the stuff that you need. Something along the lines of:

你试过加入自己吗?在第一个中,您选择包含“最后考试”的ID,然后在第二个中加入您需要的内容。有点像:

select A.* FROM
employeexam A INNER JOIN (
    SELECT EMPLOYEE_ID, MAX(DATE)
    FROM EMPLOYEEXAM
    GROUP BY EMPLOYEE_ID
) B
ON A.EMPLOYEE_ID = B.EMPLOYEE_ID AND
A.DATE = B.DATE

Assuming of course the dates per Employee_id are unique.

当然假设每个Employee_id的日期是唯一的。

0  

SELECT x.*
     , y.ttl
  FROM employeexam x 
  JOIN 
     ( SELECT course_id
            , employee_id
            , MAX(date) max_date
            , COUNT(*) ttl 
         FROM employeexam 
        GROUP 
           BY course_id
            ,employee_id
     ) y 
    ON y.course_id = x.course_id 
   AND y.employee_id = x.employee_id 
   AND y.max_date = x.date;

0  

SELECT  ee.*, num_exams
FROM    (
        SELECT  employee_id, COUNT(*) AS num_exams
        FROM    employeexam
        GROUP BY
                employee_id
        ) eed
JOIN    employeeexam ee
ON      ee.id =
        (
        SELECT  id
        FROM    employeeexam eei
        WHERE   eei.employee_id = eed.employee_id
        ORDER BY
                eei.employee_id DESC, eei.date DESC, eei.id DESC
        LIMIT 1
        )

This will handle the case of multiple exams taken on one date correctly.

这将处理在一个日期正确进行多项考试的情况。