php - 如何按年份降序和月份升序排序表格？

Question

我正在尝试从 mysql 数据库构建一个 html 表。一年中的月份将位于表格顶部(1 月至 12 月)，年份将位于左侧第一列，2015 年在顶部，2008 年在底部。目前我按日期降序排列。这意味着最新的项目将位于左上角。但是，我需要按一年中的月份按 ASC 排序，按年份按 DSC 排序。我假设我需要使用 DATE_FORMAT 但我不确定如何实现它。数据库中的日期格式是这样的:2014-07-01

            $resource_where_conditions = '`publish` = \'y\' and `category_id` = ' . $cat_data['id'];
            $date_result = $db->selectByStrings($resource_data_fields, '`resource`', $resource_where_conditions, '`date` desc');
            $current_year = '';
            $base_url_query = (_USE_SEO_URLS === true) ? $_SERVER['PHP_SELF'] . '?' : $_SERVER['REQUEST_URI'] . '&';
            if ($req->isRequest ('year')) $url_year = $req->getRequest ('year');
            if ($db->getNumRows($date_result) > 0)  {
                $copy_investor_newsletter .= $tab0 . '<table class="table table-bordered table-responsive"><tbody><thead><tr><th>January</th><th>Feburay</th><th>March</th><th>April</th><th>May</th><th>June</th><th>July</th><th>August</th><th>September</th><th>October</th><th>November</th><th>December</th></tr></thead>' . $retn;
                while ($data = $db->getNextRow($date_result))   {
                    if (substr ($data['date'], 0, 4) != $current_year)  {
                        $current_year = substr ($data['date'], 0, 4);
                        if (!isset ($latest_year)) $latest_year = $current_year;
                        if (!isset ($url_year)) {
                            $navsel = ' class="ActNav"';
                            $url_year = $current_year;
                        } elseif ($url_year == $current_year)   {
                            $navsel = ' class="ActNav"';
                            $selected_year = $url_year;
                        } else $navsel = '';
                        $copy_investor_newsletter .= $tab1 . '<tr>';
                //      if ($url_year == $current_year) {
                            /*
                             * read and display all newsletters for selected year
                             */
                            $resource_where_conditions = '`publish` = \'y\' and `category_id` = ' . $cat_data['id'] . ' and substr(`date`,1,4) = \'' . $current_year . '\'';
                            $resource_result = $db->selectByStrings($resource_data_fields, '`resource`', $resource_where_conditions, '`date` desc');
                            if ($db->getNumRows($resource_result) > 0)  {
                        //      $copy_investor_newsletter .= $retn . $tab2 . '<ul>' . $retn;
                                while ($data = $db->getNextRow($resource_result))   {
                                    $copy_investor_newsletter .= $tab3 . '<td><a href="/includes/act_download.php?fid=' . $this_fund_id . '&rid=' . $data['id'] . '&cid=' . $cat_data['id'] . '&download=' . $data['file'] . '" target="_blank">' . $data['heading'] . '</a></td>' . $retn;
                                }
                            //  $copy_investor_newsletter .= $tab2 . '</ul>' . $retn . $tab1;
                            }
            //          }
                        $copy_investor_newsletter .= '</tr>' . $retn;
                    }
                }
                $copy_investor_newsletter .= $tab0 . '</tbody></table>' . $retn . $retn;
            }

Answer 1

尝试此 SQL 查询以获得所需的顺序:

SELECT * FROM Table ORDER BY YEAR(date) DESC, MONTH(date) DESC