SQL查询本年每月的数据

--一、以一行数据的形式，显示本年的12月的数据，本示例以2017年为例，根据统计日期字段判断，计算总和，查询语句如下：
 
select
 
sum(case when  datepart(month,统计日期)=1 then 支付金额 else 0 end) as '1月',
sum(case when  datepart(month,统计日期)=2 then 支付金额 else 0 end) as '2月',
sum(case when  datepart(month,统计日期)=3 then 支付金额 else 0 end) as '3月',
sum(case when  datepart(month,统计日期)=4 then 支付金额 else 0 end) as '4月',
sum(case when  datepart(month,统计日期)=5 then 支付金额 else 0 end) as '5月',
sum(case when  datepart(month,统计日期)=6 then 支付金额 else 0 end) as '6月',
sum(case when  datepart(month,统计日期)=7 then 支付金额 else 0 end) as '7月',
sum(case when  datepart(month,统计日期)=8 then 支付金额 else 0 end) as '8月',
sum(case when  datepart(month,统计日期)=9 then 支付金额 else 0 end) as '9月',
sum(case when  datepart(month,统计日期)=10 then 支付金额 else 0 end) as '10月',
sum(case when  datepart(month,统计日期)=11 then 支付金额 else 0 end) as '11月',
sum(case when  datepart(month,统计日期)=12 then 支付金额 else 0 end) as '12月'
from TM店铺整体月指标
where datepart(year,统计日期)='2023'

--二、根据当前日期，以列的数据形式，显示本年的12个月的数据，查询语句如下：
select  
    统计日期=convert(varchar(10),dateadd(month, 0,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,1,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,2,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,3,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,4,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,5,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,6,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,7,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,8,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,9,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,10,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) 
    union all select date=convert(varchar(10),dateadd(month,11,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120)

--二、具体应用示例：以2017为例，查询语句如下：
 
 
	with t as 
( 
    select  统计日期=substring(convert(varchar(10),dateadd(month, 0,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,1,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,2,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,3,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,4,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,5,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,6,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,7,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,8,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
    union all select date=substring(convert(varchar(10),dateadd(month,9,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2)
    union all select date=substring(convert(varchar(10),dateadd(month,10,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2)
    union all select date=substring(convert(varchar(10),dateadd(month,11,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) 
)
 select id=ROW_NUMBER()OVER(ORDER BY t1.统计日期),
        统计日期=t1.统计日期+'月',     
        支付金额=sum(isnull(t2.支付金额,0)) 
        from t t1 
 
 
		left join
        (
            select substring(convert(varchar,统计日期,120),6,2) as 统计日期,sum(支付金额) as 支付金额--count(*) as counts
            from TM店铺整体月指标  
            where datepart(year,统计日期)='2023' 
            group by substring(convert(varchar,统计日期,120),6,2)  
        ) t2 
        on t1.统计日期= t2.统计日期
        group by t1.统计日期 

--sql 查询每一年1到12月的数据
Select Year(统计日期) as 年份,Month(统计日期)  as 月份,SUM(支付金额) as 支付金额
FROM TM店铺整体月指标
 
Group by Year(统计日期),Month(统计日期)

--2、每月
select year(统计日期) 年,month(统计日期) 月,
sum(支付金额) as 支付金额, sum(老买家支付金额) as 老买家支付金额,sum(支付件数) as 支付件数,
sum(直通车消耗) as 直通车消耗,sum(淘宝客佣金) as 淘宝客佣金,sum(钻石展位消耗) as 钻石展位消耗,
 
sum(成功退款金额) as 成功退款金额,
sum(PC端支付金额) as PC端支付金额,sum(无线端支付金额) as  无线端支付金额,
sum(PC端支付件数) as PC端支付件数,sum(无线端支付件数) as 无线端支付件数
 
from TM店铺整体日指标
 
WHERE
  YEAR (统计日期) = 2023   -- 这里假设你要查 2023年的每月的统计。
 
group by year(统计日期),month(统计日期)

--按年统计 
SELECT year(统计日期) 年次, 
sum(支付金额) as 支付金额, sum(老买家支付金额) as 老买家支付金额,sum(支付件数) as 支付件数,
sum(直通车消耗) as 直通车消耗,sum(淘宝客佣金) as 淘宝客佣金,sum(钻石展位消耗) as 钻石展位消耗,
 
sum(成功退款金额) as 成功退款金额,
sum(PC端支付金额) as PC端支付金额,sum(无线端支付金额) as  无线端支付金额,
sum(PC端支付件数) as PC端支付件数,sum(无线端支付件数) as 无线端支付件数
 
FROM TM店铺整体月指标 
GROUP BY year(统计日期)

--按月逐级累计查询语句：
SELECT a.统计日期, SUM(b.支付金额) as 支付金额  FROM
	(SELECT 统计日期, SUM(支付金额) 支付金额 FROM TM店铺整体月指标 GROUP BY 统计日期) a
INNER JOIN
	(SELECT 统计日期, SUM(支付金额) 支付金额 FROM TM店铺整体月指标 GROUP BY 统计日期) b
ON a.统计日期 >= b.统计日期
GROUP BY a.统计日期
ORDER BY a.统计日期

--按周统计 (年度周次)
SELECT datepart(week, 统计日期) 周次, count(1) 销售天数, 
 
sum(支付金额) as 支付金额, sum(老买家支付金额) as 老买家支付金额,sum(支付件数) as 支付件数,
sum(直通车消耗) as 直通车消耗,sum(淘宝客佣金) as 淘宝客佣金,sum(钻石展位消耗) as 钻石展位消耗,
 
sum(成功退款金额) as 成功退款金额,
sum(无线端支付金额) as  无线端支付金额,sum(PC端支付金额) as PC端支付金额,
sum(无线端支付件数) as 无线端支付件数, sum(PC端支付件数) as PC端支付件数
 
 
FROM TM店铺整体日指标 
WHERE year(统计日期)=year(getdate()) 
GROUP BY datepart(week, 统计日期) 

--按周统计 (月份周次)
SELECT weekName 周次,count(1) 销售次数, 
 
sum(支付金额) as 支付金额, sum(老买家支付金额) as 老买家支付金额,sum(支付件数) as 支付件数,
sum(直通车消耗) as 直通车消耗,sum(淘宝客佣金) as 淘宝客佣金,sum(钻石展位消耗) as 钻石展位消耗,
 
sum(成功退款金额) as 成功退款金额,
sum(无线端支付金额) as  无线端支付金额,sum(PC端支付金额) as PC端支付金额,
sum(无线端支付件数) as 无线端支付件数, sum(PC端支付件数) as PC端支付件数
 
from (
SELECT cast(datepart(month,统计日期) as varchar(2)) + '月第'+ cast((datepart(week,统计日期) - datepart(week,convert(varchar(7),统计日期,120) + '-01') + 1) as varchar(2)) + '周'  
weekName,支付金额,老买家支付金额,支付件数,直通车消耗,淘宝客佣金,钻石展位消耗,成功退款金额,无线端支付金额,PC端支付金额,无线端支付件数,PC端支付件数
FROM TM店铺整体日指标 
WHERE year(统计日期)=year(getdate()) 
)tb
GROUP BY weekName 

--按季统计 
SELECT datepart(quarter, 统计日期) 季次, count(1) 销售次数,
 
sum(支付金额) as 支付金额, sum(老买家支付金额) as 老买家支付金额,sum(支付件数) as 支付件数,
sum(直通车消耗) as 直通车消耗,sum(淘宝客佣金) as 淘宝客佣金,sum(钻石展位消耗) as 钻石展位消耗,
 
sum(成功退款金额) as 成功退款金额,
sum(无线端支付金额) as  无线端支付金额,sum(PC端支付金额) as PC端支付金额,
sum(无线端支付件数) as 无线端支付件数, sum(PC端支付件数) as PC端支付件数
 
FROM TM店铺整体月指标 
WHERE year(统计日期)=year(getdate()) 
GROUP BY datepart(quarter, 统计日期)