按年分组SQL查询结果

I'm new in SQL and I need to group the results of a query by year.

In other words, I have this table structure: id | title | date

My current query SELECT * FROM table

My current result:

array =>
  0 =>
    array =>
      ...
      'date' => string '2018-03-09'
  1 =>
    array =>
      ...
      'date' => string '2018-03-15'
  2 =>
    array =>
      ...
      'date' => string '2017-03-15'

And I need something like that:

array =>
  0 =>
    array =>
      ...
      'date' => string '2018-03-09'
    array =>
      ...
      'date' => string '2018-03-15'
  1 =>
    array =>
      ...
      'date' => string '2017-03-15'

Thanks in advance for help :)

[EDIT]

I finally found a way to do what I wanted:

$result = $sql->query("SELECT * FROM abstract ORDER BY date DESC");
$array = [];

forEach($result as $res) {
    $year = strtok($res['date'], '-');
    if (!isset($array[$year]))
        $array[$year][0] = $res;
    else
        array_push($array[$year], $res);
}
return $array;

If you want group data by year.

    Select id,title,date,DATE_FORMAT(date, '%Y') YEAR_GRP from table group by YEAR_GRP

This is something you'll want to do in your query, as MySQL can do it much more efficiently, than PHP.

The way to do it is to add a GROUP BY clause to your query, which will batch rows in your resultset into a single row, based on a grouping value, which in your case will be YEAR(date), as you're looking to group items by the year component in each line's date.

Because you're still looking to have all the results returned, merely grouped by each distinct grouping value, you'll need to use an Aggregate Function to combine all the matching lines into an array of values. Luckily MySQL has a function called json_objectagg which does exactly what you need.

So you'll end up with a query that looks something like:

SELECT year(`date`) AS `year`, json_objectagg(id, `date`, title) AS `line`
FROM abstract
GROUP BY `year`

Instead of json_objectagg, alternatives are e.g. json_arrayagg

SELECT year(`date`) AS `year`, json_arrayagg(id, `date`, title) AS `line`

...or group_concat

SELECT year(`date`) AS `year`, group_concat(id, `date`, title) AS `line`

...depending on what kind of output you require.