如果其他列中的某一列不同，则求和一列的结果

Question

如果期间代码不唯一，我想对total_students求和，如果期间代码唯一，则保持原样。例如：

SELECT t.id as tutorId,g.sem_year,g.empno,g.period_code,
CASE WHEN NOT DISTINCT(g.period_code) 
THEN sum(total_students) as student_no
ELSE total_students as student_no 
FROM tutor t  
LEFT JOIN tutor_students g ON(t.id=g.empno)
WHERE t.id=5
AND g.sem_year =6
group by period_code  

我希望student_no根据period_code有两个不同的值。这里，当period_code=555555时，我们将学生总数相加为50，因为它不是唯一的。在其他情况下，我们没有进行汇总。示例：

period_code= 555555 and total_students= 30    THEN  student_no =50
period_code= 555555 and total_students= 20
period_code= 444444 and total_students= 2     THEN  student_no =2
period_code= 333333 and total_students= 4     THEN  student_no =4
period_code= 888888 and total_students= 15    THEN  student_no =15

Answer 1

您似乎想要count():的窗口函数

SELECT t.id as tutorId, g.sem_year, g.empno, g.period_code,
COUNT(g.empno) OVER (PARTITION BY g.period_code) as student_no    
FROM tutor t INNER JOIN 
tutor_students g 
ON t.id = g.empno
WHERE t.id = 5 AND g.sem_year = 6;

编辑：您可以使用具有子查询的其他窗口功能：

SELECT t.*,
(CASE WHEN total_students <> max_student_no THEN student_sum END) AS student_no_1
FROM(SELECT t.id as tutorId, g.sem_year, g.empno, g.period_code,
SUM(total_students) OVER (PARTITION BY g.period_code) as student_sum,
MAX(total_students) OVER (PARTITION BY g.period_code) as max_student_no    
FROM tutor t INNER JOIN 
tutor_students g 
ON t.id = g.empno
WHERE t.id = 5 AND g.sem_year = 6
) t;