如何获取一年里每个月的记录总数，要求不管该月有没有数据都要显示，没有的显示为0？
如何获取一年里每个月的记录总数，要求不管该月有没有数据都要显示，没有的显示为0？
同样
怎么列出一个月里每天的数据，没有的显示为零。
看了一下你报的错误，感觉是没有COPY全。
我把格式调整了一下：
CREATE TABLE #Temp (ymd datetime)
INSERT INTO #Temp
SELECT ‘2006-01-02 ‘
UNION ALL
SELECT ‘2007-01-02 ‘
UNION ALL
SELECT ‘2007-01-02 ‘
UNION ALL
SELECT ‘2007-02-02 ‘
UNION ALL
SELECT ‘2007-03-02 ‘
UNION ALL
SELECT ‘2007-04-02 ‘
SELECT ymd.ymd,ISNULL(t.cnt,0) cnt
FROM
(
SELECT TOP 100 PERCENT CONVERT(datetime,CONVERT(varchar(4),Y.Y)+ ‘- ‘+CONVERT(varchar(2),M.M)+ ‘-01 ‘) ymd
FROM
(
SELECT DISTINCT YEAR(ymd) Y
FROM #Temp
) Y
CROSS JOIN
(
SELECT 1 AS M UNION
SELECT 2 AS M UNION
SELECT 3 AS M UNION
SELECT 4 AS M UNION
SELECT 5 AS M UNION
SELECT 6 AS M UNION
SELECT 7 AS M UNION
SELECT 8 AS M UNION
SELECT 9 AS M UNION
SELECT 10 AS M UNION
SELECT 11 AS M UNION
SELECT 12 AS M
) M
ORDER BY 1
) ymd
LEFT JOIN
(
SELECT CONVERT(varchar(7),ymd,120)+ ‘-01 ‘ ymd,COUNT(1) cnt
FROM #Temp
GROUP BY CONVERT(varchar(7),ymd,120)
) t
ON ymd.ymd = t.ymd
DROP TABLE #Temp
你自己也仔细看一下，如果还有错，我就没有办法了。
