检查列的所有值。

Question

选择具有偶数学籍的所有系。

Dept No     Roll No   Student Name 

 1         1          lee 
 1         2          scott
 2         2          scott
 2         4          smith
 1         4          smith

这应该会导致DEpt no 2，因为它只有可以被2整除的学生。

Answer 1

您可以像这样将GROUP BY与HAVING一起使用。

查询

SELECT [Dept No]
FROM departments
GROUP BY [Dept No]
HAVING SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1
AND SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END) = 0

解释

如果存在rollno，则查询返回部门，甚至使用SUM(CASE WHEN [Roll No] % 2 = 0 THEN 1 ELSE 0 END) > 1。如果有带有奇数滚动号的rollno，SUM(CASE WHEN [Roll No] % 2 = 1 THEN 1 ELSE 0 END)将返回非零和，该部门将被排除在外。

Answer 2

declare @t table (Dept int,Rno int,Student varchar(10))
insert into @t (Dept,Rno,Student)values (1,1,'lee'),(1,2,'scott'),(2,2,'scott'),(2,4,'smith'),(1,4,'smith')



SELECT Dept,Rno,Student 
FROM (SELECT ROW_NUMBER () OVER (ORDER BY Rno DESC) row_number, Dept,Rno,Student  
    FROM @t) a WHERE (row_number%2) = 0