将两个查询连接在一起

发布于 2024-11-03 09:01:08 字数 463 浏览 2 评论 0原文

我有一个数据库，其中包含下表中的大量学生信息。

Student:
    id
    created_on
    updated_on

StudentHistory
    id
    studentid
    schoolid
    gradeid
    datestamp
    active

学生历史记录管理学生多年来的变化，并允许我报告学生的成长。

我现在需要执行以下查询：

SELECT * FROM students
INNER JOIN studenthistory
    ON student.id = studenthistory.studentid
WHERE studenthistory.active = 1

问题是查询不一定会像我需要的那样提取最新的历史记录。有没有一种方法可以保证只有最新的历史记录才会被拉入连接？

原文

I have a database that holds a bunch of student information in the following tables

Student:
    id
    created_on
    updated_on

StudentHistory
    id
    studentid
    schoolid
    gradeid
    datestamp
    active

The student history manages how students change over the years and allows me to report back student growth.

I now need to do the following query:

SELECT * FROM students
INNER JOIN studenthistory
    ON student.id = studenthistory.studentid
WHERE studenthistory.active = 1

The problem is that the query won't necessarilly pull the latest history record like I need. Is there a way that I can guarentee that only the latest history record will get pulled in the join?

分享到QQ

分享到微博

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

发布评论

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

浅浅淡淡 2024-11-10 09:01:08

SELECT  *
FROM    students s
JOIN    studentshistory sh
ON      sh.id =
        (
        SELECT  shi.id
        FROM    studentshistory shi
        WHERE   shi.studentid = s.id
                AND shi.active = 1
        ORDER BY
                studentid DESC, active DESC, datestamp DESC, id DESC
        LIMIT 1
        )

在 studentshistory (studentid, active, datestamp, id) 上创建索引，以便快速工作。

SELECT  *
FROM    students s
JOIN    studentshistory sh
ON      sh.id =
        (
        SELECT  shi.id
        FROM    studentshistory shi
        WHERE   shi.studentid = s.id
                AND shi.active = 1
        ORDER BY
                studentid DESC, active DESC, datestamp DESC, id DESC
        LIMIT 1
        )

Create an index on studentshistory (studentid, active, datestamp, id) for this to work fast.

回复收藏 0 原文

听你说爱我 2024-11-10 09:01:08

我认为这应该有效：

SELECT * FROM Students
INNER JOIN StudentHistory sh1 
  ON Student.studentid = sh1.studentid 
  AND StudentHistory.datestamp = 
    (SELECT MAX(datestamp) 
     FROM StudentHistory sh2 
     WHERE sh2.studentid = sh1.studentid);

I think this should work:

SELECT * FROM Students
INNER JOIN StudentHistory sh1 
  ON Student.studentid = sh1.studentid 
  AND StudentHistory.datestamp = 
    (SELECT MAX(datestamp) 
     FROM StudentHistory sh2 
     WHERE sh2.studentid = sh1.studentid);

回复收藏 0 原文

~没有更多了~