博客文章归档SQL语句

SELECT m.y,t.m,t.num,t.text
FROM 
    (
		SELECT FROM_UNIXTIME(`tour_addtime`, '%Y') AS y,
		FROM_UNIXTIME(`tour_addtime`, '%m') AS m,
		count(`tour_id`) AS num,
		FROM_UNIXTIME(`tour_addtime`, '%Y年%m月') AS text
    FROM `hl_tour`
    GROUP BY y,m
    ORDER BY y desc,m DESC
) t
LEFT JOIN 
    (SELECT FROM_UNIXTIME(`tour_addtime`, '%Y') AS y, MAX(FROM_UNIXTIME(`tour_addtime`, '%m')) AS m
    FROM `hl_tour`
    GROUP BY y ) m
    ON t.y=m.y
        AND t.m=m.m