我需要一个SQL查询，按员工的年份分组，

Question

select datepart(yyyy,hiredate) as Hire_Date_of_Year,jobTitle,count(jobTitle) as Number_Of_Title
from [AdventureWorks2012].[HumanResources].[Employee]
group by jobTitle,hiredate
having hiredate like '2004%'
order by jobtitle asc

上面是我的密码。

我得到的输出是

Hire_Date_of_Year   jobTitle         number_of_Count
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Buyer               1
2004                Janitor             1
2004                Janitor             1
2004                Janitor             1
2004                Janitor             1

我想要的输出

Hire_Date_of_Year   jobTitle    number_of_Count
2004            Buyer               7
2004            Janitor             4

提前谢谢。

Answer 1

您不应该真的使用like进行日期比较。最好用在字符串上。下面是编写所需查询的一种方法：

select datepart(yyyy, hiredate) as Hire_Date_of_Year,
       jobTitle, count(jobTitle) as Number_Of_Title
from [AdventureWorks2012].[HumanResources].[Employee]
where datepart(yyyy, hiredate) = 2004
group by jobTitle,  datepart(yyyy, hiredate)
order by jobtitle asc;

如果需要，datepart(yyyy, hiredate) (如果您愿意)在group by中是可选的。如果不包括它，select需要将该年放入一个聚合函数中，例如max(datepart(yyyy, hiredate))。

为了提高效率，我将条件移到where子句中。您可以在聚合之后(即在having子句中)进行比较。但这意味着在进行过滤之前，group by将按所有年份进行分组。

Answer 2

你需要按年分组，而不是整个日期。

SELECT DatePart(yy, hiredate) As Hire_Date_of_Year
     , jobTitle
     , Count(jobTitle) As Number_Of_Title
FROM   [AdventureWorks2012].[HumanResources].[Employee]
WHERE  DatePart(yy, hiredate) = 2004
GROUP
    BY DatePart(yy, hiredate)
     , jobTitle

Answer 3

分组应是：

group by jobTitle,datepart(yyyy,hiredate)