按年分组我的输出

I have a DB Projects structure like (ID, projectName, projectDate (MySQL DATE), etc). When I do SQL query:

SELECT * FROM projects ORDER BY year(projectDate) DESC

Result is:

2012 Project 1
2012 Project 2
2011 Project 3 

I need group projects by year like:

2012:
Project 1
Project 2
2011:
Project 3

How I can do this without many SQL's queries for any year? I use Smarty for template output.

SELECT * FROM projects GROUP BY year(projectDate) ORDER BY year(projectDate) DESC 

ORDER BY alters the order in which items are returned.

GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).

I don't think you can do what you want to do. You seem to want five rows in your sample output, two containing the "year" values and three containing the "project" values.

These have different numbers and types of columns.

The output from a SQL query consists of a set, where all the rows have the same columns. Although you could, conceivably, concatenate all the values together into a single column, that is better done in the application.

If you just wanted the project name in this format, you could get it by doing:

select ProjectName
from (SELECT ProjectName, year(projectDate) as y, 1 as ordering
      FROM projects 
      union all
      SELECT distinct year(projectDate), year(projectDate), 0
      FROM projects
     ) p
order by y, ordering;

This should be the best way to do it

select 
  year(projectDate) year
 , GROUP_CONCAT(projectName)
from 
 projects
group by 
 year(projectDate) desc

see http://sqlfiddle.com/#!2/d11df/3 And explode in php and pass your data to smarty

select 
  year(projectDate) year
 , GROUP_CONCAT(projectName SEPARATOR '<br />') as projectName_string
from 
 projects
group by 
 year(projectDate) desc

And maybe this will work also http://sqlfiddle.com/#!2/d11df/5