每个工人的最大日期

发布于 2024-11-04 01:49:46 字数 534 浏览 1 评论 0原文

考虑两个表。 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。

那么所需的结果是：

W_ID     C_Date
1        20/12/2010
2
3
4

编辑

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

我在 Microsoft Access 中使用 SQL

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Saygoodbye 2024-11-11 01:49:46

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

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

回复收藏 0 原文

静赏你的温柔 2024-11-11 01:49:46

select W_ID,C_ID,max(C_Date)
from(select W_ID,C_ID,C_Date
    from worker
    join takes using (W_ID)
    join course using (C_ID)
)wc
group by W_ID,C_ID;

select W_ID,C_ID,max(C_Date)
from(select W_ID,C_ID,C_Date
    from worker
    join takes using (W_ID)
    join course using (C_ID)
)wc
group by W_ID,C_ID;

回复收藏 0 原文

~没有更多了~