select left(saledate,7) as '月份' ,count(1) as '数量' from 表 group by left(saledate,7)
--楼上的别名不能Group by 吧!select 月份=convert(char(7),saledate,120), 数量=count(*) from @t group by convert(char(7),saledate,120) order by 数量 desc
--不好意思,我说的是:libin_ftsafe(子陌红尘),不是我现在楼上!
select 月份=convert(char(7),saledate,120), 数量=count(*) from 表 where datediff(yy,saledate,getdate())=0 group by 月份 order by 数量 desc
select 月份=convert(char(7),saledate,120), 数量=count(*) from 表 where datediff(yy,saledate,getdate())=0 group by convert(char(7),saledate,120) order by 数量 desc
vivianfdlpw(),楼主说的是要本年度的,你的语句需要加一个条件:
select left(saledate,7) as '月份',count(1) as '数量' from 表 Where Left(saledate,4) = '2005' --或者Left(saledate,4) = Year(GetDate()) group by left(saledate,7) 以上处理的是saledate是char或varchar类型, 如果saledate是日期型,那么就应该用下面的语句 select year(saledate)+'-'+month(saledate) as '月份',count(1) as '数量' from 表 Where year(saledate) = '2005' --或者Left(saledate,4) = Year(GetDate()) group by year(saledate)+'-'+month(saledate)
,count(1) as '数量'
from 表
group by left(saledate,7)
月份=convert(char(7),saledate,120),
数量=count(*)
from
@t
group by
convert(char(7),saledate,120)
order by
数量 desc
月份=convert(char(7),saledate,120),
数量=count(*)
from
表
where
datediff(yy,saledate,getdate())=0
group by
月份
order by
数量 desc
月份=convert(char(7),saledate,120),
数量=count(*)
from
表
where
datediff(yy,saledate,getdate())=0
group by
convert(char(7),saledate,120)
order by
数量 desc
select left(saledate,7) as '月份',count(1) as '数量'
from 表 Where Left(saledate,4) = '2005' --或者Left(saledate,4) = Year(GetDate())
group by left(saledate,7)
以上处理的是saledate是char或varchar类型,
如果saledate是日期型,那么就应该用下面的语句
select year(saledate)+'-'+month(saledate) as '月份',count(1) as '数量'
from 表 Where year(saledate) = '2005' --或者Left(saledate,4) = Year(GetDate())
group by year(saledate)+'-'+month(saledate)