select
男数量=sum(case when a.sex='男' then 1 else 0 end),
女数量=sum(case when a.sex='女' then 1 else 0 end),
b.月份
from userbase a,
(
select distinct datepart(month,time) as 月份from userbase
)
b
where datepart(month,a.time)=b.月份
group by b.月份
男数量=sum(case when a.sex='男' then 1 else 0 end),
女数量=sum(case when a.sex='女' then 1 else 0 end),
b.月份
from userbase a,
(
select distinct datepart(month,time) as 月份from userbase
)
b
where datepart(month,a.time)=b.月份
group by b.月份
,女数量=sum(case sex when '女' then 1 else 0 end)
,月份=month(time)
from UserBase
group by month(time)
select
1,'男','2004-2-22' union all select
2,'女','2004-3-5' union all select
3,'男','2004-2-5' union all select
4,'男','2004-5-8' union all select
5,'女','2004-3-15'--select * from @userbaseselect
男数量=sum(case when a.sex='男' then 1 else 0 end),
女数量=sum(case when a.sex='女' then 1 else 0 end),
b.月份
from @userbase a,
(
select distinct datepart(month,time) as 月份 from @userbase
)
b
where datepart(month,a.time)=b.月份
group by b.月份测试结果
男数量 女数量 月份
----------- ----------- -----------
2 0 2
0 2 3
1 0 5(3 row(s) affected)