使用PHP MySql的高级新闻归档年/月

SELECT YEAR(FROM_UNIXTIME(date)) AS YEAR, 
                MONTH(FROM_UNIXTIME(date)) AS MONTH,
                COUNT(*) AS TOTAL 
         FROM news GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC
 


//MONTH(FROM_UNIXTIME(date)) 

YEAR  MONTH TOTAL
2020 4 522
2020 3 51
2020 2 743
2020 1 337


MONTHNAME(FROM_UNIXTIME(date)) 

YEAR  MONTH TOTAL
2020 April 522
2020 August 51
2020 February 743
2020 January 337



$newsdata = SELECT date, id, title FROM `NEWS` ORDER BY date DESC


foreach ( $newsdata as $news ) {
    $year = date('Y', $news['date']);
    $month = date('F', $news['date']);
    $nav[$year][$month][$news['id']] = $news['title'];
}


<ul>
    <?php
    foreach ( $nav as $k => $v ) {
        ?>
        <li><?php echo $k ?>
            <ul>
            <?php
            foreach ( $v as $k2 => $v2 ) {
                ?>
                <li><?php echo $k2.' ('.sizeof($v2).')' ?>
                    <ul>
                        <?php
                        foreach ( $v2 as $k3 => $v3 ) {
                            ?>
                            <li><?php echo $v3 ?></li>
                            <?php   
                        }
                        ?>
                    </ul>
                </li>
                <?php
            }
            ?>
            </ul>
        </li>
        <?php
    }
    ?>
</ul>

//http://www.cocoachina.com/articles/476602
最终效果

2013

    July (4)
         News 1 
         News 2 
         News 3 
         News 4 
    December (2)
         News 1 
         News 2 
    NovemberPrint (4)
         News 1 
         News 2 
         News 3 
         News 4 
2014
    January (8)
         News 1 
         News 2 
         News 3 
         News 4 
         News 5 
         News 6 
         News 7 
         News 8

-------------------------------------------------------------------------------------------
//http://www.deiniu.com/article/32283.htm
//PHP文章按日期(月日)SQL归档语句

select FROM_UNIXTIME(date, '%Y-%m') as addtime, count(*) as cnt from news group by FROM_UNIXTIME(date, '%Y-%m') order by addtime desc

//结果样式
addtime 添加日期  cnt 数量
2020-08            51
2020-07        402
2020-06 401
2020-05 552
2020-04 522

-------------------------------------------------------------------------------------------
PHP文章按日期（日）SQL归档
select FROM_UNIXTIME(date, '%Y-%m-%d') as addtime, count(*) as cnt from news group by FROM_UNIXTIME(date, '%Y-%m-%d') order by addtime desc

//结果样式
addtime    cnt
2020-08-19 1
2020-08-05 3
2020-08-04 6
2020-08-03 21
2020-08-02 7
2020-08-01 13

-------------------------------------------------------------------------------------------
非时间戳日期格式归档(date_format格式化日期)
//未测试
select date_format(`post_date`,'%Y%m%d') as pubtime, count(*) as cnt from wp_posts where `post_status`='publish' group by date_format(`post_date`,'%Y%m%d') order by `ID` desc
select date_format(`post_date`,'%Y%m%d') as pubtime,date_format(`post_date`,'%m 月 %d 日') as shijian,count(*) as cnt from wp_posts where `post_status`='publish' group by date_format(`post_date`,'%Y%m%d') order by `ID` desc limit 0,7