MySQL技术内幕148页

订单表有两个字段，订单日期（按月）和订单数量，需要求每个月到一年以后间隔的订单总数。

create table MonthlyOrders

(

    ordermonth date,

    ordernum int,

    primary key (ordermonth)

);

insert into MonthlyOrders select add_months(to_date('2010-02-01','yyyy-mm-dd'),rownum),rownum from dual connect by level<=24;

commit;

SQL> select * from monthlyorders;

ORDERMONTH ORDERNUM

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

01-3月 -10 1

01-4月 -10 2

01-5月 -10 3

01-6月 -10 4

01-7月 -10 5

01-8月 -10 6

01-9月 -10 7

01-10月-10 8

01-11月-10 9

01-12月-10 10

01-1月 -11 11

01-2月 -11 12

01-3月 -11 13

01-4月 -11 14

01-5月 -11 15

01-6月 -11 16

01-7月 -11 17

01-8月 -11 18

01-9月 -11 19

01-10月-11 20

01-11月-11 21

01-12月-11 22

01-1月 -12 23

01-2月 -12 24

已选择24行。

作者的解决过程如下
1.求开始和结束的范围
2.用范围和订单表做不等连接

select

a.ordermonth frommonth,

b.ordermonth tomonth,

sum(c.ordernum) orders

from

    MonthlyOrders a

    inner join MonthlyOrders b

        on add_months(a.ordermonth,11)=b.ordermonth

    inner join MonthlyOrders c

        on c.ordermonth between a.ordermonth and b.ordermonth

group by a.ordermonth,b.ordermonth;

中间过程的结果如下

SQL> select

  2 to_char(a.ordermonth,'yyyy-mm-dd') frommonth,

  3 to_char(b.ordermonth,'yyyy-mm-dd') tomonth

  4 from

  5 MonthlyOrders a

  6 inner join MonthlyOrders b

  7 on add_months(a.ordermonth,11)=b.ordermonth;

FROMMONTH TOMONTH

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

2010-03-01 2011-02-01

2010-04-01 2011-03-01

2010-05-01 2011-04-01

2010-06-01 2011-05-01

2010-07-01 2011-06-01

2010-08-01 2011-07-01

2010-09-01 2011-08-01

2010-10-01 2011-09-01

2010-11-01 2011-10-01

2010-12-01 2011-11-01

2011-01-01 2011-12-01

2011-02-01 2012-01-01

2011-03-01 2012-02-01

已选择13行。

ITPub博客

滑动订单问题

最新文章