@Transactional
@Component("statisDAO")
@Scope("prototype")
public class StatisDAO extends AbstractDAO {
    private static final String SQL_STATIS_CHANNELTYPE = "INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange) " +
    		"SELECT DBMS_RANDOM.STRING('A', 32) AS uuid, count(*) AS counter, channeltype, accountarea,sysdate AS statistime,to_char(?)||'/'||to_char(?) AS STATISRANGE " +
    		"FROM ACTIVITY WHERE txtime>=? AND txtime<=?  GROUP BY ROLLUP (channeltype,accountarea)";

    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public boolean statisChanneltype(String startDateStr,String endDateStr) {
        try {
            DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date startDate=format.parse(startDateStr+" 00:00:00");
            Date endDate=format.parse(endDateStr+" 23:59:59");
            
            Object[] values = {startDateStr,endDateStr,startDate,endDate};
            this.getSmartDAO().update("", SQL_STATIS_CHANNELTYPE, values);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
public static void main(String[] args) {
        ApplicationContext applicationContext = new FileSystemXmlApplicationContext("file:E:\\CommonContext.xml");
        StatisDAO statisDAO = (StatisDAO) applicationContext.getBean("statisDAO");
        System.out.println(statisDAO.statisChanneltype("2011-04-01", "2011-08-02"));
    }

}

按日期时长统计：

?

?

统计每个时间段得条目数，txtime是date类型，sql如下：

按小时---select to_char(txtime, 'YYYY-MM-dd hh24') a,count(*) from mytable group by to_char(txtime,'YYYY-MM-dd hh24');

按天---select to_char(txtime, 'YYYY-MM-dd') a,count(*) from mytable group by to_char(txtime,'YYYY-MM-dd');

按月---select to_char(txtime, 'YYYY-MM') a,count(*) from mytable group by to_char(txtime,'YYYY-MM');

按年---select to_char(txtime, 'YYYY') a,count(*) from mytable group by to_char(txtime,'YYYY');

按季度---select to_char(txtime, 'YYYY-q') a,count(*) from mytable group by to_char(txtime,'YYYY-q');

按周---select to_char(txtime, 'ww') a,count(*) from mytable group by to_char(txtime,'ww');

?

参考http://hi.baidu.com/qq5910225/blog/item/4a8c91d7ef0ec514a08bb74e.html

?

同字符串类型统计

?

select
SUM(CASE
WHEN city = '海口市' THEN
1
ELSE
0
END) haikou_num
,SUM(CASE
WHEN city = '广州市' THEN
1
ELSE
0
END) guangzhou_num
FROM ACTIVITY_HIS

decode方式

SQL> select id,num from test1;

ID NUM
---------- ----------
1 3
1 4
2 2
2 5
3 1
3 8

6 rows selected

SQL> select decode(grouping(id),1, '总计 ',id) id,sum(num) num
2 from test1
3 group by rollup(id);

ID NUM
---------------------------------------- ----------
1 7
2 7
3 9
总计 23

<!--StartFragment -->

最终大神：select count(*), province, city from ACTIVITY_HIS where to_char(txtime, 'YYYY-MM-dd')='2011-06-01' group by rollup (province,city);

rollup是数据挖掘中的上卷操作，运行效果截图

?

另外，将取出来得数据直接插入表中：

select ...into 用在存储过程里面的,保存变量
insert ...select 就是插入语句,插入的部分是表中的数据

?

举例来说：

insert 表 select * from 表的方法主要有两种：

1、若两张表完全相同：
insert into table1
select * from table2
where condition(条件)

2、若两张表字段有不同的：
insert into table1(字段1，字段2，字段3....)
select 字段1，字段2，字段3....
from table2
where condition(条件)

上述两种方法均不需要写values.

综上，我的sql是：

INSERT INTO channeltypestatis(uuid,counter,channeltype,accountarea,statistime,statisrange)

SELECT DBMS_RANDOM.STRING(