文章/答案/技术大牛

发布

社区首页 >问答首页 >在具有子查询的第一条记录的三年内找到最后一条记录

问在具有子查询的第一条记录的三年内找到最后一条记录
EN

Database Administration用户

提问于 2018-07-31 15:54:25

回答 1查看 86关注 0票数 0

我有下表和学生注册数据：

CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

我也想

找出该学生入学的第一年。
查找所有注册的最新注册，这些注册发生在第一年后的3年内。
获取2中注册的DepartureCode。

上述样本数据的输出如下：

+--------------+--------------+--------------+--------------+---------------+
| SequenceID   | SchoolYear   | StudentID    | ProgramID    | DepartureCode |
+--------------+--------------+--------------+--------------+---------------+
|     37105    |         2014 |     9800384  |         5331 |           01  |
|     37320    |         2014 |     9100926  |         5729 |            01 |
+--------------+--------------+--------------+--------------+---------------+

这是第一次注册后3年内最后一次注册的全部记录。

我这样做的子查询如下，但想知道是否有另一种方式更容易阅读？

SELECT
    MainQueryEnrollments .StudentID,
    MainQueryEnrollments .ProgramID,
    MainQueryEnrollments .SchoolYear,
    MainQueryEnrollments .DepartureCode
FROM
    Enrollments AS MainQueryEnrollments
JOIN
    ( -- find SequenceID of the last enrollment within 
      -- three years of the first enrollment
    SELECT
        SubQueryEnrollments.StudentID,
        SubQueryEnrollments.ProgramID,
        MAX(SubQueryEnrollments SequenceID) AS SequenceID
    FROM
        Enrollments AS SubQueryEnrollments
    JOIN
        ( -- find the SchoolYear of the first enrollment
          SELECT
            StudentID,
            ProgramID,
            MIN(SchoolYear) AS SchoolYear
        FROM Enrollments
        GROUP BY
            StudentID,
            ProgramID
        ) FirstEnrollment
    ON FirstEnrollment.StudentID = SubQueryEnrollments.StudentID
    AND FirstEnrollment.ProgramID = SubQueryEnrollments.ProgramID
    AND SubQueryEnrollments.SchoolYear - FirstEnrollment.SchoolYear <= 2  
      -- enrollments within three years of the first enrollment
    GROUP BY
        SubQueryEnrollments .StudentID,
        SubQueryEnrollments .ProgramID
) AS LastEnrollment3Years
ON MainQueryEnrollments.SequenceID = LastEnrollment3Years.SequenceID

sql-server

sql-server-2014

subquery

回答 1

Database Administration用户

回答已采纳

发布于 2018-08-01 10:45:50

我的解决方案做了以下假设(如果我误解了您的要求，请告诉我)

SequenceId就像一个身份值，代表学生变化的时间顺序。
您希望找到每个学生的注册行，其中SequenceId是每个学生的MAX(SequenceId)，而SchoolYear位于该学生初始注册年后的3年内。

SET NOCOUNT ON
DROP TABLE IF EXISTS [dbo].[Enrollments];
CREATE TABLE [dbo].[Enrollments](
    [SequenceID] [bigint] NULL,
    [SchoolYear] [int] NULL,
    [StudentID] [varchar](8) NULL,
    [ProgramID] [varchar](10) NULL,
    [DepartureCode] [varchar](2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29735, 2012, N'9800384', N'5331', N'10')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33183, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (33549, 2013, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37105, 2014, N'9800384', N'5331', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37810, 2015, N'9800384', N'5331', N'22')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (29544, 2012, N'9100926', N'5729', N'01')
GO
INSERT [dbo].[Enrollments] ([SequenceID], [SchoolYear], [StudentID], [ProgramID], [DepartureCode]) VALUES (37320, 2014, N'9100926', N'5729', N'01')
GO

;

WITH YearFirstEnrolled
AS (
    SELECT StudentID
        ,min(SchoolYear) AS YearFirstEnrolled
    FROM Enrollments
    GROUP BY StudentID
    )
SELECT *
FROM YearFirstEnrolled fe
JOIN Enrollments e
    ON e.StudentID = fe.StudentID
        AND e.SequenceID = (
            SELECT max(SequenceID)
            FROM Enrollments
            WHERE StudentID = e.StudentID
                AND SchoolYear < fe.YearFirstEnrolled + 3
            )
order by e.SequenceID

| StudentID | YearFirstEnrolled | SequenceID | SchoolYear | StudentID | ProgramID | DepartureCode |
|-----------|-------------------|------------|------------|-----------|-----------|---------------|
| 9800384   | 2012              | 37105      | 2014       | 9800384   | 5331      | 01            |
| 9100926   | 2012              | 37320      | 2014       | 9100926   | 5729      | 01            |

从结果中选择所需的列。

票数 0

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/213679

复制

相似问题

问在具有子查询的第一条记录的三年内找到最后一条记录
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在具有子查询的第一条记录的三年内找到最后一条记录EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在具有子查询的第一条记录的三年内找到最后一条记录
EN