select sum(ttmoney), t.pk_customer, add_months( to_date(min(t.ordertime),'yyyy-MM-dd HH24:mi:ss'), -1) from table t ,(select min( t.ordertime) D from table) A where t.ordertime between add_months(A.D, -1) and A.D group by t.pk_customer
你的ordertime类型是char型的吗? char型需要to_date to_char进行转化一下 select sum(ttmoney), t.pk_customer, add_months( to_date(min(t.ordertime),'yyyy-MM-dd HH24:mi:ss'), -1) from table t ,(select min( t.ordertime) D from table) A where t.ordertime between to_char(add_months( to_date(A.D,'yyyy-MM-dd HH24:mi:ss'), -1),'yyyy-MM-dd HH24:mi:ss') and A.D group by t.pk_customer
好像少点啥,指定日期不是sysdate select sum(ttmoney), t.pk_customer, add_months( to_date(min(t.ordertime),'yyyy-MM-dd HH24:mi:ss'), -1) from table t ,(select min( ordertime) D from table where ordertime<>sysdate ) A where t.ordertime between to_char(add_months( to_date(A.D,'yyyy-MM-dd HH24:mi:ss'), -1),'yyyy-MM-dd HH24:mi:ss') and A.D group by t.pk_customer
from table t ,(select min( t.ordertime) D from table) A
where t.ordertime between add_months(A.D, -1) and A.D
group by t.pk_customer
char型需要to_date to_char进行转化一下
select sum(ttmoney), t.pk_customer, add_months( to_date(min(t.ordertime),'yyyy-MM-dd HH24:mi:ss'), -1)
from table t ,(select min( t.ordertime) D from table) A
where t.ordertime between to_char(add_months( to_date(A.D,'yyyy-MM-dd HH24:mi:ss'), -1),'yyyy-MM-dd HH24:mi:ss') and A.D
group by t.pk_customer
select sum(ttmoney), t.pk_customer, add_months( to_date(min(t.ordertime),'yyyy-MM-dd HH24:mi:ss'), -1)
from table t ,(select min( ordertime) D from table
where ordertime<>sysdate ) A
where t.ordertime between
to_char(add_months( to_date(A.D,'yyyy-MM-dd HH24:mi:ss'), -1),'yyyy-MM-dd HH24:mi:ss') and A.D
group by t.pk_customer
<=表中的最小时间,得不到多少数据吧