SQL Server月份合计

Question

SQL Server新手

以下查询仅在学科表中存在学生的记录时才按学生和月份返回SRA。我需要一个查询，以返回所有学生和月度总数，即使在学科表中没有学生的记录。任何方向都很感谢

SELECT  TOP 100 PERCENT MONTH(dbo.Discipline.DisciplineDate) AS [Month], dbo.Discipline.StuId, dbo.Stu.Lastname + ',' + dbo.Stu.FirstName AS Student, 
    SUM(CASE WHEN Discipline.SRA = 1 THEN 1 END) AS [Acad Suspension], SUM(CASE WHEN Discipline.SRA = 2 THEN 1 END) AS Conduct, 
    SUM(CASE WHEN Discipline.SRA = 3 THEN 1 END) AS Disrespect, SUM(CASE WHEN Discipline.SRA = 4 THEN 1 END) AS [S.R.A], 
    SUM(CASE WHEN Discipline.SRA = 5 THEN 1 END) AS Suspension, SUM(CASE WHEN Discipline.SRA = 6 THEN 1 END) AS Tone
FROM dbo.Discipline INNER JOIN
     dbo.Stu ON dbo.Discipline.StuId = dbo.Stu.StuId
GROUP BY dbo.Discipline.StuId, dbo.Stu.Lastname, dbo.Stu.FirstName, MONTH(dbo.Discipline.DisciplineDate)
ORDER BY Student

Answer 1

您需要将dbo.Stu上的INNER JOIN更改为LEFT JOIN

   SELECT MONTH(d.disciplinedate) AS [Month], 
          d.StuId, 
          s.Lastname + ',' + s.FirstName AS Student, 
          SUM(CASE WHEN d.SRA = 1 THEN 1 END) AS [Acad Suspension], 
          SUM(CASE WHEN d.SRA = 2 THEN 1 END) AS Conduct, 
          SUM(CASE WHEN d.SRA = 3 THEN 1 END) AS Disrespect, 
          SUM(CASE WHEN d.SRA = 4 THEN 1 END) AS [S.R.A], 
          SUM(CASE WHEN d.SRA = 5 THEN 1 END) AS Suspension, 
          SUM(CASE WHEN d.SRA = 6 THEN 1 END) AS Tone
     FROM dbo.Discipline d
LEFT JOIN dbo.Stu s ON s.stuid = d.stuid
 GROUP BY d.StuId, s.Lastname, s.FirstName, MONTH(d.DisciplineDate)
 ORDER BY Student

左连接意味着无论您将JOINing留给哪个表，它都可能没有支持该连接的记录，但是您仍然可以从基表(dbo.Discipline)中获得记录。

我使用了表别名- d和s。当您需要指定引用时，可以减少输入。

Answer 2

生成一系列的月，加入纪律。