假设我有一个employee
表,其中一列DateOfJoining
.我已将数据输入为
2019-12-4
2019-12-6
2019-12-5
2019-10-5
2010-08-17
现在,我想编写SQL查询以查找加入者数量最多的月份。
您可以在下面尝试 - 使用聚合和 TOP
select top 1 month(dateofjoining),count(*) as totaljoining
from tablename
where year(dateofjoining)=2019
group by month(dateofjoining)
order by 2 desc
使用 Derived table
和 row_number()
下面的查询将为您提供每年最多加入人数的月份。
select cnt,mnth,yr
from
(select count(DateOfJoining)cnt,
month(DateOfJoining)mnth,
year(DateOfJoining)yr,
row_number()over(partition by year(DateOfJoining) order by count(DateOfJoining)desc)srno
from #employee
group by month(DateOfJoining),year(DateOfJoining)
)tbl
where srno = 1
输出
cnt mnth yr
----------- ----------- -----------
1 8 2010
3 12 2019
如果您专门想要 2019 年,请在 where 子句中添加条件yr ='2019'
。
where srno = 1
and yr =2019
输出
cnt mnth yr
----------- ----------- -----------
3 12 2019
你想要最大和最少的 - 尽管我猜你至少想要一个员工。
with e as (
select year(dateofjoining) as yyyy,
month(dateofjoining) as mm,
count(*) as totaljoining
from employee
where dateofjoining >= '2019-01-01' and
dateofjoining < '2020-01-01'
group by year(dateofjoining), month(dateofjoining)
)
select e.*
from ((select top (1) e.*
from e
order by totaljoining asc
) union all
(select top (1) e.*
from e
order by totaljoining desc
)
) e;
笔记:
- 日期比较使用
- 与日期的直接比较,而不是使用函数。 这是最佳做法,因此优化程序可以使用索引。
- 您需要最小值和最大值,因此使用 CTE,因此
group by
仅表示一次。 - 这不会返回没有员工的月份。
- 如果您想要领带,请使用
top (1) with ties
.