在透视查询中获得错误的输出

Question

考勤表样数据-

+--------------+-------------+-----------+------------+------------+
| EnrollmentNo | SubjectCode |   Date    | Attendance | CourseCode |
+--------------+-------------+-----------+------------+------------+
| DDU-140/12   | CSHT101     | 1/9/2018  |          1 | BSCCS      |
| DDU-140/12   | CSHT101     | 1/10/2018 |          1 | BSCCS      |
| DDU-140/12   | CSHT101     | 1/11/2018 |          2 | BSCCS      |
| DDU-286/12   | CSHT101     | 1/9/2018  |          1 | BSCCS      |
| DDU-286/12   | CSHT101     | 1/10/2018 |          1 | BSCCS      |
| DDU-286/12   | CSHT101     | 1/11/2018 |          0 | BSCCS      |
| DDU-286/12   | CSHT102     | 1/11/2018 |          1 | BSCCS      |
| DDU-286/12   | CSHT102     | 1/14/2018 |          2 | BSCCS      |
| DDU-320/12   | CSHT101     | 1/9/2018  |          2 | BSCCS      |
| DDU-320/12   | CSHT101     | 1/10/2018 |          1 | BSCCS      |
| DDU-320/12   | CSHT101     | 1/11/2018 |          0 | BSCCS      |
| DDU-320/12   | CSHT102     | 1/11/2018 |          1 | BSCCS      |
| DDU-320/12   | CSHT102     | 1/14/2018 |          0 | BSCCS      |
+--------------+-------------+-----------+------------+------------+

学生表样本数据-

+--------------+--------+-------------+------------+------+
| EnrollmentNo | RollNO | CollegeCode | CourseCode | Year |
+--------------+--------+-------------+------------+------+
| DDU-140/12   |     22 | DDUC        | BSCCS      | 2012 |
| DDU-286/12   |     15 | DDUC        | BSCCS      | 2012 |
| DDU-320/12   |     38 | DDUC        | BSCCS      | 2012 |
+--------------+--------+-------------+------------+------+

StudentSubject表格示例数据-

+--------------+-------------+
| EnrollmentNo | SubjectCode |
+--------------+-------------+
| DDU-140/12   | CSHT101     |
| DDU-286/12   | CSHT101     |
| DDU-286/12   | CSHT102     |
| DDU-320/12   | CSHT101     |
| DDU-320/12   | CSHT102     |
+--------------+-------------+

主题表样本数据-

+-------------+---------------------------+
| SubjectCode |        SubjectName        |
+-------------+---------------------------+
| CSHP101     | Software Lab based on 101 |
| CSHT101     | Programming Fundamentals  |
| CSHT102     | Discrete Structures       |
+-------------+---------------------------+

输出-

​

​

错误的输出与参数一起显示-

@enrollno = 'DDU-320/12',
@startdate = '1/9/2018',
@enddate = '1/11/2018'

应该显示As 2,1,0和NA，NA,1，而不是显示2,1,1和2,1,1。

不能指出我正在犯的错误。有人能看一下吗？

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_STUDENT
@enrollno varchar(10),
@startdate DATE,  
@enddate DATE

AS BEGIN

DECLARE @query as varchar(MAX);

with cte (startdate) as 
(
    select @startdate startdate
    union all 
    select dateadd(DD, 1, startdate) 
    from cte
    where startdate < @enddate
)

select @query = coalesce(@query, '') + 
              N',coalesce(MAX(CASE when A.[Date] = ''' + 
              cast(cte.startdate as nvarchar(20)) + 
              N''' THEN Convert(varchar(10),A.[Attendance]) end), ''NA'') ' + 
              quotename(convert(char(6), cte.startdate,106))
from cte
where datename(weekday, cte.startdate) <> 'Sunday';

set @query = 'Select Concat(S.SubjectCode,'' '',S.SubjectName) Subject' + @query + '
              from Attendance A, Student St, StudentSubject SS, Subject S
              where A.EnrollmentNo=St.EnrollmentNo and St.EnrollmentNo=SS.EnrollmentNo and SS.SubjectCode=S.SubjectCode and St.EnrollmentNo =''' + @enrollno + '''
              Group By S.SubjectName,S.SubjectCode';


Execute (@query)
END

Answer 1

缺少联接条件，还需要使用SubjectCode联接Attendance表。由于缺少条件，它从两个SubjectCodes中选取日期。

set @query = '
SELECT Concat(S.SubjectCode, '' '', S.SubjectName) Subject
       '+ @query +'
FROM   Attendance A
       INNER JOIN Student St
               ON A.EnrollmentNo = St.EnrollmentNo
       INNER JOIN StudentSubject SS
               ON St.EnrollmentNo = SS.EnrollmentNo
                  AND a.SubjectCode = ss.SubjectCode --here
       INNER JOIN Subject S
               ON SS.SubjectCode = S.SubjectCode
WHERE  St.EnrollmentNo =''' + @enrollno + '''
GROUP  BY S.SubjectName,
          S.SubjectCode'

Execute (@query)

另外，在旧式的逗号分隔连接上开始使用INNER JOIN语法。在ON子句中保留连接条件，在Where子句中保留过滤器。

话虽如此，根据您的预期结果，您不需要将这两个表放在第一位