在MySQL中按年份排列 [英] Group by year in date field in MySQL

问题描述

我有一个MySQL数据库，它有一个客户表。一些虚拟数据是：

  customer_id日期
 000001 2008-10-10 
 000002 2008-11- 11 
 000003 2010-01-02 
 000004 2007-04-03 
 000005 2010-05-05 
  

我想运行一个查询，这将给我一个结果，如下所示：

  year customer_count 
 2007 1 
 2008 2 
 2010 2 
  

我知道我需要使用group by，但是我无法围绕如何根据日期字段的年值分组，以及如何让它们顺序。

解决方案

从日期和组合中提取年份

 选择年份）作为年份，
计数（customer_id）作为客户
 from your_table 
 group by year 
 order by year asc 
  

I have a MySQL database which has a customer table. Some dummy data is:

customer_id    date
000001         2008-10-10
000002         2008-11-11
000003         2010-01-02
000004         2007-04-03
000005         2010-05-05

I want to run a query which will give me a result like so:

year    customer_count
2007    1
2008    2
2010    2

I know I need to use group by, however I am unable to wrap my head around how to group based on year value of a date field, and how to have them in an order.

解决方案

Extract the year from the date and group by it

select year(date) as year, 
       count(customer_id) as customers
from your_table
group by year
order by year asc

这篇关于在MySQL中按年份排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！