关于一个sql语句的问题
0
悬赏园豆：5
[已关闭问题]
关闭于 2011-09-26 16:11
我现在有这样的一个需求，有三个字段 id,name,times(时间：如：2010-8-29 10：20：10)
我现在要按时间进行分组，统计每一个月的信息，该咋办？
所有回答(4)
0
统计每个月信息，
SELECT COUNT(*) ,SUBSTRING( CONVERT(VARCHAR,times,110),1,2) FROM 表名
GROUP BY SUBSTRING( CONVERT(VARCHAR,times,110),1,2)
0
with your_table(id,name,times) as (
select 0 , 'killkill' , cast('2010-08-01' as datetime) union all
select 1 , 'killkill' , cast('2010-09-01' as datetime) union all
select 2 , 'killkill' , cast('2010-10-01' as datetime)
)
select DATEPART(YYYY,times),DATEPART(MM,times),COUNT(*)
from your_table
group by DATEPART(YYYY,times),DATEPART(MM,times)
----------- ----------- -----------
2010 8 1
2010 9 1
2010 10 1
0
with yourtable(id,name,times) as (
select 0 , 'name1' , cast('2010-01-01' as datetime) union all
select 1 , 'name2' , cast('2010-02-01' as datetime) union all
select 2 , 'name3' , cast('2010-03-01' as datetime)
select 4 , 'name3' , cast('2010-04-01' as datetime)
)
SELECT COUNT(*), CONVERT(varchar(100), times, 23) from yourtable
group by CONVERT(varchar(100), times, 23)
---------------------- -----------
2010-01-01 1
2010-02-01 1
2010-03-01 1
2010-04-01 1
