首页 新闻 搜索 专区 学院

关于一个sql语句的问题

0
悬赏园豆:5 [已关闭问题] 关闭于 2011-09-26 16:11

我现在有这样的一个需求,有三个字段 id,name,times(时间:如:2010-8-29 10:20:10)

我现在要按时间进行分组,统计每一个月的信息,该咋办?

Impossible的主页 Impossible | 初学一级 | 园豆:72
提问于:2010-08-29 08:56
< >
分享
所有回答(4)
0

统计每个月信息,

SELECT COUNT(*) ,SUBSTRING( CONVERT(VARCHAR,times,110),1,2) FROM 表名
GROUP BY SUBSTRING( CONVERT(VARCHAR,times,110),1,2) 

jeff-zheng | 园豆:480 (菜鸟二级) | 2010-08-29 10:14
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

 

killkill | 园豆:992 (小虾三级) | 2010-08-29 13:15
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

 

jowo | 园豆:2834 (老鸟四级) | 2010-08-29 16:05
0
喬喬AI | 园豆:996 (小虾三级) | 2011-08-20 01:20
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册