SQL查询语句 - 根据月份分组

-------------------------------------------------------------------

源文地址: http://www.cnblogs.com/yunlei0821/p/7644509.html ，转载请务必保留此出处.

• 建表:

  drop table if exists tt_max_price;
  create table tt_max_price(
      id int(11) not null AUTO_INCREMENT,
      dt datetime,
      price float,
      primary key(id)
  );

   

• 模拟数据:

  insert into tt_max_price(dt,price) VALUES('2015-01-01',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-02',23.2);
  insert into tt_max_price(dt,price) VALUES('2015-01-03',23.3);
  insert into tt_max_price(dt,price) VALUES('2015-01-04',13.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-05',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-06',253.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-07',221.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-08',2233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-09',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-10',23.54);
  insert into tt_max_price(dt,price) VALUES('2015-01-11',23.45);
  insert into tt_max_price(dt,price) VALUES('2015-01-12',123.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-13',223.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-14',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-15',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-16',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-17',2343.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-18',243.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-19',2113.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-20',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-21',2333.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-22',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-23',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-24',231.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-25',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-26',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-27',223.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-28',323.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-29',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-30',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-01-31',2903.5);
  
  insert into tt_max_price(dt,price) VALUES('2015-02-01',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-02',23.2);
  insert into tt_max_price(dt,price) VALUES('2015-02-03',23.3);
  insert into tt_max_price(dt,price) VALUES('2015-02-04',13.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-05',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-06',253.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-07',221.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-08',2233.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-09',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-10',23.54);
  insert into tt_max_price(dt,price) VALUES('2015-02-11',23.45);
  insert into tt_max_price(dt,price) VALUES('2015-02-12',123.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-13',223.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-14',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-15',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-16',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-17',23643.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-18',243.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-19',2113.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-20',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-21',2333.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-22',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-23',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-24',231.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-25',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-26',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-27',223.5);
  insert into tt_max_price(dt,price) VALUES('2015-02-28',323.5);
  
  insert into tt_max_price(dt,price) VALUES('2015-03-01',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-02',23.2);
  insert into tt_max_price(dt,price) VALUES('2015-03-03',23.3);
  insert into tt_max_price(dt,price) VALUES('2015-03-04',13.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-05',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-06',253.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-07',221.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-08',2233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-09',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-10',23.54);
  insert into tt_max_price(dt,price) VALUES('2015-03-11',23.45);
  insert into tt_max_price(dt,price) VALUES('2015-03-12',123.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-13',223.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-14',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-15',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-16',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-17',2343.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-18',243.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-19',2113.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-20',23.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-21',2333.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-22',213.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-23',29003.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-24',231.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-25',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-26',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-27',223.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-28',323.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-29',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-30',233.5);
  insert into tt_max_price(dt,price) VALUES('2015-03-31',23.5);

   

• 查询:

1. 1. 查询每个月存在记录的最后一天:

      select a.* from tt_max_price a,
      (select max(dt) dtb from tt_max_price where price is not null group by extract(month from dt)) b
      where a.dt=b.dtb;

       

   2. 查询每个月price记录的最大值:

select a.* from tt_max_price a,
(select max(price) bp from tt_max_price where price is not null group by extract(month from dt)) b
where a.price=b.bp;