SQLSERVER中按年月分组
一个表有三个字段id,dt,d 分别存放id,时间,数值
ID | DT | d |
1 | 2015-08-11 12:12:00.000 | 9 |
2 | 2016-09-11 12:12:00.000 | 2 |
3 | 2017-10-11 12:12:00.000 | 6 |
4 | 2018-11-11 12:12:00.000 | 10 |
5 | 2019-12-11 12:12:00.000 | 0 |
要求按照时间里的月份分组求d字段和:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[abc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[abc]
GO
CREATE TABLE [dbo].[abc] (
[id] [int] NOT NULL ,
[dt] [datetime] NULL ,
[d] [int] NULL
) ON [PRIMARY]
GO
insert into abc (id,dt,d) values(1,'2004-08-11 12:12:00',9)
insert into abc (id,dt,d) values(2,'2005-09-11 12:8:00',2)
insert into abc (id,dt,d) values(3,'2005-08-11 12:12:00',6)
insert into abc (id,dt,d) values(4,'2005-09-11 12:12:00',10)
insert into abc (id,dt,d) values(5,'2005-08-11 12:12:00',0)
insert into abc (id,dt,d) values(6,'2004-11-2 12:12:00',4)
insert into abc (id,dt,d) values(7,'2004-11-10 12:12:00',4)
insert into abc (id,dt,d) values(8,'2004-11-30 12:12:00',4)
select * from abc
select datepart(month,dt)as 月份,sum(d) as 合计 from abc group by datepart(month,dt)
其实就用了一个DATEPART函数
引申一下:如果统计1,2,3,4,5,6,7,8,9,10,11月上旬,11月中下旬,12月的怎么办?
可以这样:
select case datepart(month,dt)
when 11 then case sign(datepart(day,dt)-11) when -1 then 11 else 13 end
else datepart(month,dt) end as 月份,
sum(d) as 统计
from abc group by
case datepart(month,dt)
when 11 then case sign(datepart(day,dt)-11) when -1 then 11 else 13 end
else datepart(month,dt) end
再引申,如果统计把年月作为分组统计的依据可以这样:
select datename(year,dt)+datename(month,dt)as 年月 ,sum(d) as 统计 from abc group by datename(year,dt)+datename(month,dt)
最后,明白group by 后面不仅可以跟字段名就可以了。
再问再续:
1、按照旬统计
select case (datepart(day,dt)-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end as 旬,
sum(d) as 统计
from abc group by
case (datepart(day,dt)-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end
2、按 年+旬 分组统计
select datename(year,dt)+datename(month,dt)+case (datepart(day,dt)-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end as 日期, sum(d) as 统计
from abc
group by datename(year,dt)+datename(month,dt)+case (datepart(day,dt)-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end