关于取出一年内每个月的记录数，可否用一条SQL实现吗？

Question

现有一个订单表，内容很简单ID       Name       Date
1        Cup        2007-1-1
2        Glasses    2007-4-15
3        Sock       2007-6-1我想根据一条SQL，得到一年12个月的记录数，并不是光这三个月的，而是
2007-1       1
2007-2       0
2007-3       0
2007-4       1
2007-5       0
2007-6       1
2007-...     0
2007-12      0有什么方法实现吗？或有什么好的思路，我知道如果有一个空的月份表，可以实现用left join左联实现，其它的就不知道了
数据库 Mysql 5

Answer 1

--SQL SERVER 2000
SELECT TT.[MONTH],ISNULL(SS.COUNT(1),0) AS COU
FROM
(
SELECT CONVERT(VARCHAR(7),DATEADD(MONTH,ID,'2007-01-01'),120) AS [MONTH]
FROM
(SELECT TOP 12 ID = (SELECT COUNT(1) FROM SYSOBJECTS WHERE ID < A.ID)
 FROM SYSOBJECTS A
)T
)TT LEFT JOIN 订单表 SS ON TT.[MONTH] = CONVERT(VARCHAR(7),SS.DATE,120)
GROUP BY TT.[MONTH]

Answer 2

可以用select "2007-01" union select "2007-02"的方式去构建虚拟表,再left join 
订单表..其实lz的方法可行就行啦,加个参照表就可以解决多好啊,为什么非要在一条语句里实现.

Answer 3

其实二楼的方法就不错，因为SQLServer有SELECT TOP 12 ID = (SELECT COUNT(1) FROM SYSOBJECTS WHERE ID < A.ID)
 FROM SYSOBJECTS A这种语法，可以取出 1-12 这12个数，但mysql没有，还要再建个表，我就是不想建表通过子查询什么的查出来，呵呵，不知道还没有有好办法，现在我会先建个参照表，然后用二楼的方法实现

Answer 4

你可以先查处一个按找日期的YYYY-MM的数据试图 
然后再统计,如下例统计每月出现的人名的次数.
select count(a.username) ,a.date
from (
select username,substr(date,1,7) date
from mytest ) a
group by a.datemysql5.0.27版本测试OK