从具有多行的列中选择一行

发布于 2025-01-29 17:59:02 字数 878 浏览 2 评论 0原文

我是SQL新手，所以请忍受。

我正在编写一个选择语句以从多个表中选择数据，但是当我尝试选择特定列时，我会得到重复，因为该列可以正确地有多个行。我要做的是选择最合适的行，然后选择。

到目前为止，我的代码：

Select 
    a.[StudentId], a.[Name], a.[StartDT], a.[EndDT], 
    b.[ClassID], b.[Module], b.[ModStart], b.[ModEnd]
from
    [Data].[StudentTbl] a
left join 
    [Data].[ClassTbl] b on a.[StudentId] = b.[Student_ID]

当我选择b。[模块]时，我会得到多行，因为每个类可能有许多模块，但是我想选择b。模块]学生在离开之前已经完成。

从本质上讲，如果a。[endDt]等于b。[modend]，我需要该特定行。最大函数并不总是有效，因为classtbl中存在DQ问题，即在最后一个模块说N/A之后插入了一排时，

我当前得到的是：

< a href =“ https://i.sstatic.net/ch6j2.png” rel =“ nofollow noreferrer”>

我最终想得到的：

原文

I'm a SQL newbie so bear with me.

I am writing a select statement to select data from multiple tables which I have done however when I try to select a specific column I get duplicates as that column can rightly have multiple rows. What I want to do is select the most appropriate row and select that.

My code so far:

Select 
    a.[StudentId], a.[Name], a.[StartDT], a.[EndDT], 
    b.[ClassID], b.[Module], b.[ModStart], b.[ModEnd]
from
    [Data].[StudentTbl] a
left join 
    [Data].[ClassTbl] b on a.[StudentId] = b.[Student_ID]

When I select the b.[Module] I'm getting multiple rows as there can be a number of modules per class however I am wanting to select the b.[Module] the student has completed before leaving.

Essentially if the a.[EndDT] is equal to b.[ModEnd], I need that specific row. Max function doesn't always work as there are DQ issues within the ClassTbl that when a student has left a row is inserted after the last module saying N/A

What I'm currently getting is this: