DECLARE @CURRENTSCHOOL TABLE (STUDENT VARCHAR(8), COURSE VARCHAR(8), SCHOOL VARCHAR(2))
INSERT INTO @CURRENTSCHOOL VALUES ('10000000','MCR1010','11')
INSERT INTO @CURRENTSCHOOL VALUES ('12000000','MCR6080','11')
INSERT INTO @CURRENTSCHOOL VALUES ('13000000','MCR6090','15')

DECLARE @OTHERSCHOOLS TABLE (STUDENT VARCHAR(8), COURSE VARCHAR(8), SCHOOL VARCHAR(2))
INSERT INTO @OTHERSCHOOLS VALUES ('10000000','MCR1010','11')
INSERT INTO @OTHERSCHOOLS VALUES ('10000000','MCR1011','14')
INSERT INTO @OTHERSCHOOLS VALUES ('10000000','MCR1012','15')
INSERT INTO @OTHERSCHOOLS VALUES ('12000000','MCR6080','19')
INSERT INTO @OTHERSCHOOLS VALUES ('13000000','MCR6090','15')


对于以上示例数据。两张桌子。当前学校和其他学校。

当前学校是学生正在学习的当前课程,包括学校代码,
是主表。

其他学校可能是学生可以在其他学校继续学习的其他课程。

我需要将当前学校表与使用学生ID编号匹配的其他学校表进行比较,并且对于其他学校中的每个不同学校代码,都需要返回一个计数。

例如:

Student:    OtherSchoolCount:
10000000    2        (because of 2 different school codes than than the current school)
12000000    1        (because of 1 different school code than than the current school)
13000000    blank    (because not a different school code)


这可能吗?

非常感谢

M.

最佳答案

SELECT cs.student,
       COUNT(os.course)
FROM   @CURRENTSCHOOL cs
       LEFT JOIN @OTHERSCHOOLS os
         ON cs.student = os.student
            AND cs.school <> os.school
GROUP  BY cs.student


输出

STUDENT
-------- -----------
10000000 2
12000000 1
13000000 0


如果Null确实比Null更受青睐,那么您可以执行此操作(或使用等效的CTE)

SELECT student,
       CASE
         WHEN coursecount = 0 THEN NULL
         ELSE coursecount
       END coursecount
FROM   (SELECT cs.student,
               COUNT(os.course) coursecount
        FROM   @CURRENTSCHOOL cs
               LEFT JOIN @OTHERSCHOOLS os
                 ON cs.student = os.student
                    AND cs.school <> os.school
        GROUP  BY cs.student) t


哪些输出

student  courseCount
-------- -----------
10000000 2
12000000 1
13000000 NULL


更新:NullIF可以用作Case语句的替代方法,请参见What applications are there for NULLIF()?

10-06 05:50