COUNT和GROUP的不同用法

Question

与我之前发布的另一个问题非常相似，但更复杂(我认为！)

我有一组表：

表1

 StudentId   Student     Course
 1234567     J. Bloggs   ABC      
 1234567     J. Bloggs   ABC       
 2345678     T. Test     ABC       

表2

StudentId    Student       Module      Result      StartDate     EndDate
1234567      J. Bloggs     DEF         Fail        13/01/2014    13/03/2014
1234567      J. Bloggs     DEF         Pass        01/05/2014    15/07/2014
2345678      T. Test       DEF         Pass        13/01/2014    13/03/2014

我想要做的是让SQL返回，首先是学生在模块中尝试了多少次，然后返回最近的EndDate和结果，就像这样。

Student     Course    Module      ModuleAttempts       Result       EndDate
J. Bloggs   ABC       ABC               2              Pass         15/07/2014
T. Test     ABC       ABC               1              Pass         13/03/2014

编辑:添加初始表布局

Answer 1

这应该可以做到：

;WITH CTE AS
(
    SELECT  *,
            ModuleAttempts = COUNT(*) OVER(PARTITION BY Student, Module),
            RN = ROW_NUMBER() OVER(PARTITION BY Student, Module
                                   ORDER BY EndDate DESC)
    FROM dbo.YourTable
)
SELECT  Student,
        Course,
        Module,
        ModuleAttempts,
        Result,
        EndDate
FROM CTE
WHERE RN = 1;

Answer 2

这就对了：SQL Fiddle Demo

SELECT Student, Course, Module, 
(SELECT COUNT(*) FROM myTable t2 WHERE t2.Student = t1.Student) as ModuleAttempts, 
CASE WHEN CAST(MAX(CAST(RESULT as INT)) AS BIT) = 1 THEN 'PASS' ELSE 'Fail' END as Result,
MAX(EndDate) as EndDate
FROM MyTable t1
GROUP By Student, Course, Module

备注：

在我的示例中，SELECT COUNT结果是一个位列(Pass = 1，Failed =0)语句：(SELECT COUNT(*)

• myTable t2 WHERE t2.Student = t1.Student)应为t2.ID = t1.ID，而不是t2.Student =t1.Student。

Answer 3

Lamak的答案是正确的，但由于结果只包含fail和pass，下面的查询也可以。

    SELECT  Student,
            Course,
            Module,
           count(ModuleAttempts) as ModuleAttempts,
           max(Result) as Result,
--max(Result) will return "pass" (if pass exists) as it comes after fail in dictionary 
           max(EndDate) as EndDate
    from table
    group by Student,
            Course,
            Module