MAX工作人员的日期 [英] MAX Date for each worker

问题描述

考虑两个表。工作表有 W_ID，W_Name 等字段。课程表格包含 C_ID，C_Name，C_Date 等字段。

工人可以选择许多课程，课程可以被许多工人带走因此，两张表之间有许多关系。打破许多到许多，并创建一个名为Takes的新表，由外键W_ID和C_ID组成。

这里是一个问题：我想找出一个列表的所有工人和他们最后的课程。例如，如果W_ID = 1，C_Date 20/01/2010和C_Date 25/10/2010和C_Date 20/12/2010。

然后，所需的结果是： / p>

  W_ID C_Date 
 1 20/12/2010 
 2 
 3 
 4 
  

修改

我想找到工作人员完成课程的最长日期，因为工作人员将会完成许多课程。我是商务学生，所以我不能使用INNER JOIN命令。 IN可以替代使用？

我在Microsoft Access中使用我的SQL

解决方案

  SELECT w.w_id，MAX（c.c_date）
 FROM worker w 
 LEFT JOIN 
 worker_course wc 
 ON wc。 w_id = w.w_id 
 LEFT JOIN 
 courseс
 ON c.c_id = wc.c_id 
 GROUP BY 
 w.w_id 
  

或此：

  SELECT w .w_id，MAX（c.c_date）
 FROM courseс
 JOIN worker_course wc 
 ON wc.c_id = c.c_id 
 RIGHT JOIN 
 worker w 
 ON w.w_id = wc.w_id 
 GROUP BY 
 w.w_id 
  

Consider two tables. Worker table has fields like W_ID, W_Name. Course table has fields like C_ID, C_Name, C_Date.

A Worker can take many courses and a course can be taken by many Workers. Therefore, there is a many to many relationship between the two tables. Break the many to many, and create a new table called Takes which consists of the foreign keys W_ID and C_ID.

Here is the question: I want to find out a list of all workers and their last course taken. For example if W_ID=1 took C_Date 20/01/2010 and C_Date 25/10/2010 and C_Date 20/12/2010.

Then the Result required is:

W_ID     C_Date
1        20/12/2010
2
3
4

Edit

I want to find the maximum date of the course each worker has done as workers would have done many courses. I am a business student, so I can't use INNER JOIN command. IN can be used instead?

I am using my SQL in Microsoft Access

解决方案

SELECT  w.w_id, MAX(c.c_date)
FROM    worker w
LEFT JOIN
        worker_course wc
ON      wc.w_id = w.w_id
LEFT JOIN
        course с
ON      c.c_id = wc.c_id
GROUP BY
        w.w_id

or this:

SELECT  w.w_id, MAX(c.c_date)
FROM    course с
JOIN    worker_course wc
ON      wc.c_id = c.c_id
RIGHT JOIN
        worker w
ON      w.w_id = wc.w_id
GROUP BY
        w.w_id

这篇关于MAX工作人员的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！