SQL Server 2005 - 如何比较字段值,如果不同,返回一个计数,用于每次出现 [英] SQL Server 2005 - how to compare field value, and return a count if different, for every occurance
问题描述
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')
对于上述示例数据。两个表。当前学校和其他学校。
For the above sample data. Two tables. Currentschool and Otherschools.
当前学校是学生目前的课程,包括学校代码
,是主表。
Currentschool is the current course that a student is on including the schoolcode, and is the main table.
其他学校是潜在的其他课程,学生可以在不同的学校继续。
OtherSchools is potentially other courses that a student can go on, in differing schools.
我需要比较当前学校表与otherschools表
I need to compare the currentschool table against the otherschools table matched using the student id number, and for every different schoolcode in otherschools, it needs to return a count.
例如:
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比Zero真的优先,那么你可以这样做或使用等效的CTE)
If Null is really preferred over Zero then you can do this (or use the equivalent 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语句使用,请参阅应用程序有NULLIF()吗?
Update: NullIF could be put to use as alternative to the Case statement see What applications are there for NULLIF()?
这篇关于SQL Server 2005 - 如何比较字段值,如果不同,返回一个计数,用于每次出现的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!