参考select
name,
sum(case day(日期) when 1 then 销售额 end) as [1],
sum(case day(日期) when 2 then 销售额 end) as [2],
sum(case day(日期) when 3 then 销售额 end) as [3],
sum(case day(日期) when 4 then 销售额 end) as [4],
sum(case day(日期) when 5 then 销售额 end) as [5],
sum(case day(日期) when 6 then 销售额 end) as [6],
sum(case day(日期) when 7 then 销售额 end) as [7],
sum(case day(日期) when 8 then 销售额 end) as [8],
sum(case day(日期) when 9 then 销售额 end) as [9],
sum(case day(日期) when 10 then 销售额 end) as [10],
sum(case day(日期) when 11 then 销售额 end) as [11],
sum(case day(日期) when 12 then 销售额 end) as [12],
sum(case day(日期) when 13 then 销售额 end) as [13],
sum(case day(日期) when 14 then 销售额 end) as [14],
sum(case day(日期) when 15 then 销售额 end) as [15],
sum(case day(日期) when 16 then 销售额 end) as [16],
sum(case day(日期) when 17 then 销售额 end) as [17],
sum(case day(日期) when 18 then 销售额 end) as [18],
sum(case day(日期) when 19 then 销售额 end) as [19],
sum(case day(日期) when 20 then 销售额 end) as [20],
sum(case day(日期) when 21 then 销售额 end) as [21],
sum(case day(日期) when 22 then 销售额 end) as [22],
sum(case day(日期) when 23 then 销售额 end) as [23],
sum(case day(日期) when 24 then 销售额 end) as [24],
sum(case day(日期) when 25 then 销售额 end) as [25],
sum(case day(日期) when 26 then 销售额 end) as [26],
sum(case day(日期) when 27 then 销售额 end) as [27],
sum(case day(日期) when 28 then 销售额 end) as [28],
sum(case day(日期) when 29 then 销售额 end) as [29],
sum(case day(日期) when 30 then 销售额 end) as [30],
sum(case day(日期) when 31 then 销售额 end) as [31]
from tb
where datediff(mm,日期,getdate())=0
group by name
name,
sum(case day(日期) when 1 then 销售额 end) as [1],
sum(case day(日期) when 2 then 销售额 end) as [2],
sum(case day(日期) when 3 then 销售额 end) as [3],
sum(case day(日期) when 4 then 销售额 end) as [4],
sum(case day(日期) when 5 then 销售额 end) as [5],
sum(case day(日期) when 6 then 销售额 end) as [6],
sum(case day(日期) when 7 then 销售额 end) as [7],
sum(case day(日期) when 8 then 销售额 end) as [8],
sum(case day(日期) when 9 then 销售额 end) as [9],
sum(case day(日期) when 10 then 销售额 end) as [10],
sum(case day(日期) when 11 then 销售额 end) as [11],
sum(case day(日期) when 12 then 销售额 end) as [12],
sum(case day(日期) when 13 then 销售额 end) as [13],
sum(case day(日期) when 14 then 销售额 end) as [14],
sum(case day(日期) when 15 then 销售额 end) as [15],
sum(case day(日期) when 16 then 销售额 end) as [16],
sum(case day(日期) when 17 then 销售额 end) as [17],
sum(case day(日期) when 18 then 销售额 end) as [18],
sum(case day(日期) when 19 then 销售额 end) as [19],
sum(case day(日期) when 20 then 销售额 end) as [20],
sum(case day(日期) when 21 then 销售额 end) as [21],
sum(case day(日期) when 22 then 销售额 end) as [22],
sum(case day(日期) when 23 then 销售额 end) as [23],
sum(case day(日期) when 24 then 销售额 end) as [24],
sum(case day(日期) when 25 then 销售额 end) as [25],
sum(case day(日期) when 26 then 销售额 end) as [26],
sum(case day(日期) when 27 then 销售额 end) as [27],
sum(case day(日期) when 28 then 销售额 end) as [28],
sum(case day(日期) when 29 then 销售额 end) as [29],
sum(case day(日期) when 30 then 销售额 end) as [30],
sum(case day(日期) when 31 then 销售额 end) as [31]
from tb
where datediff(mm,日期,getdate())=0
group by name
select patno ,
sum(case MONTH(日期) when 1 then 1 else 0 end) as '1月',
sum(case MONTH(日期) when 2 then 1 else 0 end) as '2月',
sum(case MONTH(日期) when 3 then 1 else 0 end) as '3月',
sum(case MONTH(日期) when 4 then 1 else 0 end) as '4月',
sum(case MONTH(日期) when 5 then 1 else 0 end) as '5月',
sum(case MONTH(日期) when 6 then 1 else 0 end) as '6月',
sum(case MONTH(日期) when 7 then 1 else 0 end) as '7月',
sum(case MONTH(日期) when 8 then 1 else 0 end) as '8月',
sum(case MONTH(日期) when 9 then 1 else 0 end) as '9月',
sum(case MONTH(日期) when 10 then 1 else 0 end) as '10月',
sum(case MONTH(日期) when 11 then 1 else 0 end) as '11月',
sum(case MONTH(日期) when 12 then 1 else 0 end) as '12月'
from cz_main
where convert(char(10),日期,120) like '2007%'
group by patno
from cz_main
where convert(char(10),日期,120) like '2007%'
order by
group by MONTH(日期)
if object_id('[T_Product]') is not null drop table [T_Product]
go
create table [T_Product]([产品ID] int,[产品名称] varchar(3),[添加时间] datetime)
insert [T_Product]
select 1,'abc','2009-1-2' union all
select 13,'abc','2008-12-2' union all
select 14,'abc','2009-12-2'
---查询---
declare @year int;
set @year=2009
select 月份=1,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=1),0)
union all
select 月份=2,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=2),0)
union all
select 月份=3,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=3),0)
union all
select 月份=4,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=4),0)
union all
select 月份=5,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=5),0)
union all
select 月份=6,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=6),0)
union all
select 月份=7,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=7),0)
union all
select 月份=8,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=8),0)
union all
select 月份=9,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=9),0)
union all
select 月份=10,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=10),0)
union all
select 月份=11,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=11),0)
union all
select 月份=12,商品个数=isnull((select count(1) from T_Product where year(添加时间)=@year and month(添加时间)=12),0)---结果---
月份 商品个数
----------- -----------
1 1
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 1(所影响的行数为 12 行)
sum(case month(添加时间) when 1 then 1 else 0 end) as [1],
sum(case month(添加时间) when 2 then 1 else 0 end) as [2],
sum(case month(添加时间) when 3 then 1 else 0 end) as [3],
sum(case month(添加时间) when 4 then 1 else 0 end) as [4],
sum(case month(添加时间) when 5 then 1 else 0 end) as [5],
sum(case month(添加时间) when 6 then 1 else 0 end) as [6],
sum(case month(添加时间) when 7 then 1 else 0 end) as [7],
sum(case month(添加时间) when 8 then 1 else 0 end) as [8],
sum(case month(添加时间) when 9 then 1 else 0 end) as [9],
sum(case month(添加时间) when 10 then 1 else 0 end) as [10],
sum(case month(添加时间) when 11 then 1 else 0 end) as [11],
sum(case month(添加时间) when 12 then 1 else 0 end) as [12]
from T_Product
where datepart(year,添加时间)=2007
group by month(添加时间)
from cz_main
where convert(char(10),日期,120) like '2007%'
group by MONTH(日期)