将数据拆分成组

Question

我有代码

SELECT     TOP (100) PERCENT dbo.census.Group_Code, dbo.census.Gender, SUM(dbo.v_Courses.Dur_In_Hours) 
                      AS SumOf_Dur_In_Hours
FROM         dbo.v_Courses INNER JOIN
                      dbo.census ON dbo.v_Courses.Job_Group_Code = dbo.census.Group_Code
WHERE     (dbo.v_Courses.System = 'GEMS') AND (NOT (dbo.v_Courses.Course_ID LIKE 'UPS%')) AND 
                      (dbo.v_Courses.First_Access_date BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-31 00:00:00', 
                      102))
GROUP BY dbo.census.Group_Code, dbo.census.Gender
ORDER BY dbo.census.Group_Code

它的目的是给我提供我的工作级别和性别的总小时数，但每个性别的工作级别都返回相同的小时数。我想不出怎么才能让他们分开。

所以结果看起来有点像这样，但不可能每个性别都有相同的小时数。

Job     Gen         Hours
11  F   10886.50417
11  M   10886.50417
16  F   33494.17222
16  M   33494.17222
16  U   33494.17222
21  F   19267.76361
21  M   19267.76361

是性别造成了重复。

Answer 1

你能在内部连接中包含性别吗？或者v_courses没有这个字段？

SELECT     TOP (100) PERCENT dbo.census.Group_Code, dbo.census.Gender, SUM(dbo.v_Courses.Dur_In_Hours) 
                  AS SumOf_Dur_In_Hours
FROM         dbo.v_Courses 
INNER JOIN dbo.census 
   ON dbo.v_Courses.Job_Group_Code = dbo.census.Group_Code
   AND dbo.v_Courses.Gender = dbo.census.Gender
WHERE     (dbo.v_Courses.System = 'GEMS') AND (NOT (dbo.v_Courses.Course_ID LIKE 'UPS%')) AND 
                  (dbo.v_Courses.First_Access_date BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-31 00:00:00', 
                  102))
GROUP BY dbo.census.Group_Code, dbo.census.Gender
ORDER BY dbo.census.Group_Code

Answer 2

如果在没有组的情况下运行查询，是否会看到重复的行？如果是这样，那么在join/where中可能需要另一个限制。