用年和月来排序，后台SQL语句怎么写好？

[dovefay]

我现在在C#里写的是

SELECT TOP 3 MONTH(CLICK_TIME),COUNT(*)
  FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc

但这种是以月份来排的，在SQL SERVER里出来的是

(No column name) (No column name)
12 140
11 151
10 130

但我想要出来的是这样，以当前时间的月份为起点，向后3个月
2009-12 140
2009-11 151
2009-10 130

所以这种的SQL语句要怎么写好？

[sdnjiejie65]

第一种方法是:
SELECT TOP 3 datename(year,CLICK_TIME)+'-'+datename(Month,getdate()),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc
第二种方法是:
SELECT TOP 3 Convert(varchar(7),getdate(),120),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc

呵呵,你试试

[zhuxiaojun2002]

select count(1) as y_axis, CONVERT(char(7), start_date, 120) as x_axis
from device_alarm
where start_date between @START_DATE and @END_DATE and EARTH_STATION_ID=@Station_ID
group by CONVERT(char(7), start_date, 120)

[zhuxiaojun2002]

select count(1) as num, CONVERT(char(7), start_date, 120) as ym
from tb
group by CONVERT(char(7), start_date, 120)

[zhuxiaojun2002]

select count(1) as num, CONVERT(char(7), start_date, 120) as ym
from tb
where datediff(month,start_date,getdate()) <3
group by CONVERT(char(7), start_date, 120)

[zzxap]

select left(日期,7) ... group by left(日期,7)

[ivws_19]

[Quote=引用 4 楼 zzxap 的回复:]
select left(日期,7) ...  group by left(日期,7)
[/Quote]
这样就可以了

[dovefay]

有用是有用，可我这边做的是美国项目，时期格式有问题，没年份出现

(No column name) (No column name)
Nov 30 14
Nov 25 1
Nov 24 34
Nov 23 26
Nov 21 22
Nov 19 12


[Quote=引用 4 楼 zzxap 的回复:]
select left(日期,7) ...  group by left(日期,7)
[/Quote]

[dovefay]

还是不行，日期格式不对。

因为开发的的美国方面的项目，机器全部都是英文，时区和格式也是美国的。

用select * FORM RECORD 语句出来的是,像是这种

CLIENT_IP	CLICK_TIME	        SOURCE	EVENT	COUNT
209.71.1.89	2009-11-17 14:23:29.000	  a1	NULL	1
99.62.4.10	2009-11-17 14:26:38.000	  a2	NULL	2
99.62.4.10	2009-11-17 14:27:31.000	  a2	NULL	3

但用你的第一个语句报错啊,

第二个语句是可以运行，但是，151那个应当是11月份的，为什么也是12月份了？
(No column name) (No column name)
2009-12 171
2009-12 151




[Quote=引用 7 楼 sdnjiejie65 的回复:]
第一种方法是:
SELECT TOP 3 datename(year,CLICK_TIME)+'-'+datename(Month,getdate()),COUNT(*)
  FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc
第二种方法是:
SELECT TOP 3 Convert(varchar(7),getdate(),120),COUNT(*)
  FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc

呵呵,你试试
[/Quote]

[dovefay]

/****** Script for SelectTopNRows command from SSMS  ******/
  SELECT  DATENAME(year,getdate())+'-'+DATENAME(MONTH,getdate()),COUNT(*) 
  FROM [iptimes].[dbo].[RECORD] where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc 

返回的结果是

(No column name) (No column name)
2009-December 172
2009-December 151

可日期没有递减啊，

[dovefay]

而且不能这种写法不能跨年显示，永远都是以当前的年份开头，不会有2010，也不有2008

[sdnjiejie65]

不会呀,

你可能是有的getdate()--是显示当前年月
第二种方法:
SELECT TOP 3 Convert(varchar(7),getdate(),120),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc
/**
getdate()====>应该改成你表里的时间字段CLICK_TIME

**/

应该是这样:
SELECT TOP 3 Convert(varchar(7),CLICK_TIME,120),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc

[sdnjiejie65]

原来的:

第一种方法是:
SELECT TOP 3 datename(year,CLICK_TIME)+'-'+datename(Month,getdate()),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc
第二种方法是:
SELECT TOP 3 Convert(varchar(7),getdate(),120),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc
现在的:


第一种方法是:
SELECT TOP 3 datename(year,CLICK_TIME)+'-'+datename(Month,CLICK_TIME),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc
第二种方法是:
SELECT TOP 3 Convert(varchar(7),CLICK_TIME,120),COUNT(*)
FROM RECORD where SOURCE='AAA' group by MONTH(CLICK_TIME) order by MONTH(CLICK_TIME) desc

你再试试看,如果行,请给分哦,呵