sql server 按年月日分组

本文介绍了一种在 SQL Server 中根据不同的时间维度(年、月、日)进行数据分组的方法,通过存储过程实现对特定活动期间各种奖品发放数量的统计。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

sql server  按年月日分组

-----------------------------------------------
--author:yangjinwang
--date:2017-03-30
--do:根据活动查询 每个奖品类的发放数量
-----------------------------------------------
create proc GetWinningInfoTypeCountByTimeActivity
@CreateTimeStart datetime =null,  --开始时间
@CreateTimeEnd datetime =null,  --结束时间
@TimeType varchar(20)='m',   --查询维度,年月日
@ActivityId int=null         --活动ID
as
begin
    if(@TimeType='y')
        begin
            select  
            c.id as '活动ID',
            c.Name  as '活动名称',
            cast(datepart(YEAR,a.CreateTime) as varchar(4))   as  '日期',
            b.Title as  '奖品类别'
            ,COUNT(a.Id) as '中奖数量'
            from  WinningInfo  a 
            left join PrizesInfo b on a.PrizesId=b.Id
            left join ActivityInfo  c on b.ActivityId=c.Id
            where  a.ActivityId=@ActivityId
            and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null)
            and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null)
             group by c.id,c.Name  ,  
            cast(datepart(YEAR,a.CreateTime) as varchar(4)) ,
            b.Title
            order by 日期
        end
        else if(@TimeType='m')
            begin
                select  
                c.id as '活动ID',
                c.Name  as '活动名称',
                cast(datepart(YEAR,a.CreateTime) as varchar(4))+'-'+RIGHT('00'+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)     as  '日期',
                b.Title as  '奖品类别'
                ,COUNT(a.Id) as '中奖数量'
                from  WinningInfo  a 
                left join PrizesInfo b on a.PrizesId=b.Id
                left join ActivityInfo  c on b.ActivityId=c.Id
                where  a.ActivityId=@ActivityId
                and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null)
                and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null)
                 group by c.id,c.Name  ,  
                cast(datepart(YEAR,a.CreateTime) as varchar(4))+'-'+RIGHT('00'+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)  ,
                b.Title
                order by 日期    
            end
        else
            begin
                    select  
                c.id as '活动ID',
                c.Name  as '活动名称',
                cast(datepart(YEAR,a.CreateTime) as varchar(4))+'-'+RIGHT('00'+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)+'-'+RIGHT('00'+CAST(day(a.CreateTime) AS VARCHAR(2)),2)     as  '日期',
                b.Title as  '奖品类别'
                ,COUNT(a.Id) as '中奖数量'
                from  WinningInfo  a 
                left join PrizesInfo b on a.PrizesId=b.Id
                left join ActivityInfo  c on b.ActivityId=c.Id
                where  a.ActivityId=@ActivityId
                and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null)
                and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null)
                 group by c.id,c.Name  ,  
                cast(datepart(YEAR,a.CreateTime) as varchar(4))+'-'+RIGHT('00'+CAST(MONTH(a.CreateTime) AS VARCHAR(2)),2)+'-'+RIGHT('00'+CAST(day(a.CreateTime) AS VARCHAR(2)),2)   ,
                b.Title
                order by 日期    
            end    
end

 年,月,日,季,旬

--还可以这样
--
select datepart(YEAR,getdate())
select datepart(yyyy,getdate())
select datepart(yy,getdate())
--
select datepart(MONTH,getdate())
select datepart(mm,getdate())
select datepart(m,getdate())
--
select datepart(dd,getdate())
--1年中的第多少天
select datepart(dy,getdate())
--季度
select datepart(qq,getdate())

select datepart(qq,'2017-07-01')
--1年中的第多少周
select datepart(wk,getdate())
--星期 --因为从周日算第一天的,星期4,计算结果是5
select datepart(dw,getdate())-1


SELECT CONVERT(VARCHAR(10),GETDATE(),120)  --2015-07-13
SELECT CONVERT(VARCHAR(10),GETDATE(),101)  --07/13/2015


--按日分组:
select convert(nvarchar(10),GETDATE(),120)
--按年月分组:
select  cast(datepart(YEAR,GETDATE()) as varchar(4))+'-'+RIGHT('00'+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) 
--按年分组
select DATEPART(year,GETDATE())


--按旬分组
 select case (datepart(day,GETDATE())-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end as 旬,
 sum(1) as 统计 
 from 表A group by 
 case (datepart(day,dt)-1)/10 when 0 then '上旬' when 1 then '中旬' else '下旬' end 
  
------------------------------------------------------------------------------------

 

 另一种Convert 年月日分组方式

--按日
select convert(nvarchar(10),GETDATE(),120)
--按月
select convert(nvarchar(7),GETDATE(),120)
--按年
select convert(nvarchar(4),GETDATE(),120)

 

转载于:https://www.cnblogs.com/yangjinwang/p/6648408.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值