按日期对一组数据进行分组

问题描述：

The question is a little on the noobish end as I'm just starting to mess with PHP. However my project is to create a basic statistics page. So let me go ahead and explain exactly what I'm looking for (in it's most basic form).

Basically I have an table with the following structure. [ TransactionID, Date, Amount ]
What I want to do is group everything by a certain date and display all of the transactions that happened on that date, so I believe the query would look something like this.

Select * from table where date = 2014-05-24

Alright, that's all fine and dandy and I can easily get the sum like that, but How would I get the transactions for a certain month, or year even?

This is a personal project and won't be used at all on a professional scale, but it's something that I'm working on as a hobby.

问题是关于noobish end的一点，因为我刚刚开始乱用PHP。但是我的项目是创建一个基本的统计页面。所以，让我继续解释我正在寻找的东西（它是最基本的形式）。

基本上我有一个具有以下结构的表。 [TransactionID，Date，Amount] code> 我想要做的是按特定日期对所有内容进行分组，并显示该日期发生的所有事务，所以我相信查询会看起来像什么像这样。 p>

 从表中选择*，其中日期= 2014-05-24 
  code>  pre> 
 
 好的，这就是全部 很好，花花公子，我很容易得到这样的总和，但我怎么能得到一个月甚至一年的交易？   p> 
 
 
这是一个个人项目，根本不会在专业范围内使用，但这是我作为业余爱好所做的事情。 p> 
   DIV>

答

If your Date column is of type DateTime, you may see date functions in Mysql.

To get all records for a specific date like 2014-05-24 :

Select * from table T where Date(T.date) = Date('2014-05-24')

To get all records for a specific year like 2014 :

Select * from table T where Year(T.date) = Year('2014-05-24')

For a specific month of year like 5th of 2014

Select * from table T where Year(T.date) = Year('2014-05-24') 
and 
Month(T.date) = Month('2014-05-24')

答

Try using the EXTRACT() FUNCTION, e.g.

     select * from table where EXTRACT(YEAR FROM `date`) = '2014'

     select * from table where EXTRACT(YEAR_MONTH FROM `date`) = '201405'

see date and time functions

答

There are better ways than my example bellow, but a basic way could be :

SELECT * FROM tablename WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59

按日期对一组数据进行分组

相关推荐