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

最新推荐文章于 2022-11-16 15:28:29 发布

weixin_30685029

最新推荐文章于 2022-11-16 15:28:29 发布

阅读量527

点赞数

原文链接：http://www.cnblogs.com/yunlei0821/p/7644509.html

版权

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

源文地址: 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. 查询每个月存在记录的最后一天:
  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;

转载于:https://www.cnblogs.com/yunlei0821/p/7644509.html

weixin_30685029

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL查询语句 - 根据月份分组

-------------------------------------------------------------------源文地址:http://www.cnblogs.com/yunlei0821/p/7644509.html，转载请务必保留此出处.建表:drop table if exists tt_max_price;create tabl...
复制链接

扫一扫