sql - 如何仅过滤表格中的首次出现

Question

表结构如下:

tblApplicants:

applicantID (index)   |     ApplyingForYear (nvarchar) 
------------------------------------------------------
1                           2013/14
11                          2013/14
13                          2013/14
12                          2013/14
15                          2013/14
21                          2012/13

tblApplicantSchools_shadow:

id (index)   |    applicantID    |    updated (datetime)   |   statusID (int)   |   schoolID (int)
-----------------------------------------------------------------------------------------------------
1                   11                  2012-09-24 00:00:00.000     3                   2
1                   13                  2012-10-24 00:00:00.000     4                   2
2                   15                  2012-11-24 00:00:00.000     3                   4
3                   13                  2012-03-24 00:00:00.000     4                   3
4                   12                  2012-09-24 00:00:00.000     4                   1
5                   21                  2012-11-03 00:00:00.000     5                   2
6                   11                  2012-09-04 00:00:00.000     4                   4

我需要做的是:

• 获取所有在 tblApplicants 中 ApplyingForYear 为 '2013/14' 的申请人
• statusID 为 4
• 我只想计算它们一次 - 即使它们在 tblApplicantschools_show
中出现两次或更多次
• 按更新日期列(按周分组)对不同申请人的数量进行分组(按照上述)

所以根据上面的示例数据，应该有 3 行出来，(因为 ApplicantID 13 出现了两次，我只想要他一次)。

结果应该是这样的:

    Datesubmitted              TotalAppsPerWeek
    -------------------------------------------------------
    2012-10-24 00:00:00.000    1
    2012-09-24 00:00:00.000    1
    2012-09-04 00:00:00.000    1

这是我目前所拥有的 - 但结果是 4 行，而不是 3 :(

select  
   DATEADD(ww,(DATEDIFF(ww,0,[tblApplicantSchools_shadow].updated)),0) AS Datesubmitted,
   count(DISTINCT [tblApplicantSchools_shadow].applicantID) as TotalAppsPerWeek 
FROM tblApplicants  
INNER JOIN tblApplicantSchools_shadow 
      ON tblApplicantS.ApplicantID = tblApplicantSchools_shadow.applicantID
WHERE 
     ApplyingForYear = '2013/14' 
     AND [tblApplicantSchools_shadow].statusID = 4
GROUP BY 
     DATEADD(ww, (DATEDIFF(ww, 0, [tblApplicantSchools_shadow].updated)), 0)

这是一个 fiddle :http://sqlfiddle.com/#!3/3aa61/42

Answer 1

根据您的标题，我假设您希望每个申请人的一行是具有最小 id 的行。您可以使用 ROW_NUMBER() 函数为每个申请人 ID 选择一行:

;with latestApplication AS
(
    SELECT DATEADD(ww,(DATEDIFF(ww,0,[tblApplicantSchools_shadow].updated)),0) 
               AS Datesubmitted,
        [tblApplicantSchools_shadow].applicantID,
        ROW_NUMBER() OVER (PARTITION BY [tblApplicantSchools_shadow].applicantID 
                           ORDER BY [tblApplicantSchools_shadow].id) 
            AS rn
    FROM tblApplicants  
    INNER JOIN tblApplicantSchools_shadow 
        ON tblApplicantS.ApplicantID = tblApplicantSchools_shadow.applicantID
    WHERE ApplyingForYear = '2013/14' 
    AND [tblApplicantSchools_shadow].statusID = 4
)
select Datesubmitted, COUNT(1) AS TotalAppsPerWeek
FROM latestApplication
WHERE rn = 1
group by Datesubmitted
order by Datesubmitted DESC

http://sqlfiddle.com/#!3/3aa61/57