怎样用group by实现如下的功能？

[Struggling]

问你一句sql语句,下面是一些记录的日期，表table1

id date

25 2005-3-28
24 2005-2-18
23 2004-12-11
22 2004-12-1
21 2003-3-25
20 2003-8-28
19 2002-4-9
18 2002-10-23


我要像下面那样显示内容:
2005
3
2
2004
12
2003
3
8
2002
4
10

[baikaishui_0825]

如果date是数据类型
select year(date),month(date),day(date) from table1

[baikaishui_0825]

日期类型，打错了

[tianch]

你上面的那种结构估计不能实现
SELECT year([date]) as year,month([date]) as month from 表名 group by year([date]),month([date])
这条语句能实现以下结构
year month
2005 3
2005 2
2004 12
2003 3
2003 8
2002 4
2002 10

[tianch]

加上排序
select * from (SELECT year([date]) as year,month([date]) as month from 表名 group by year([date]),month([date])) as a order by a.[year] desc,a.[month] desc