《oracle sql精华.docx》由会员分享,可在线阅读,更多相关《oracle sql精华.docx(52页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、SQL按月统计(2009-08-12 23:32:40) 转载标签: sql按月统计oracleit分类: Oracle 前提: created_date 是 表 A 的一个日期字段1、按月分组统计 (1234等自然月统计) select sum(quantity) from A group by trunc(created_date,mm); 用 trunc 函数 将日期转化为 每个月的第一天(或者说是忽略月(mm)后面得日期);2、如果 需求是要 按 每个月 的25号来划分的话(实际经常碰到) select sum(quantity) fromAgroup by trunc(created
2、_date+(add_months(created_date,1)-created_date-20),mm); 20 的作用是 每个月按20号 分割月之间 如果是按每个月的15号分割 那么 20 的位置就是15; 蓝色字体的意思是求得 所给日期 created_date 所在月的天数; 减去20 是求得 还需要 再加上 多少天就到下个月了; JDBC 连接过程(oracle)2009-01-18 21:10:47|分类: 专题 JDBC |标签: |字号大中小订阅 1.new JavaProject (JDBC) 2.src- new class (TestJDBC) 3.import jav
3、a.sql.*; (jdbc 用到的所有的类库) 4.引入驱动包 (SQLPLUS 属性看Oracle安装路径) 1.C:oracleora92jdbclibclasses12.jar 2.要用jar包里的内容,必须加到classpath里 3.eclipse 里要用jar包,必须加到BuildPath 里 4.JDBC-BuildPath-Add External Archives -classes12.jar 5.classes12 类库,找到oracle.jdbc.driver -oracleDriver.class 类 6.产生类的对象(2种) 1.Class.forName(orac
4、le.jdbc.driver.OracleDriver); /马用这种 Class not class (key word) java.lang Class 类 类装载器,调用forName方法,根据字符串,把你类的实例new出来 forName () throws ClassNotFoundException (先throws Exception) new 实例的时候Driver 自动向DriverManager 注册 2. (常用)new oracle.jdbc.driver.OracleDriver(); 7.以上就拿到了跟数据库的连接 8.Connection conn = Drive
5、rManager.getConnection(数据库连接字符串,用户名,密码) Connection conn = DriverManager.getConnection (jdbc:oracle:thin:localhost:1521:AOOPLE,scott,tiger); 9.执行SQL 语句 Statement stmt = conn.createStatement(); / 定义在前面 ResultSet rs = stmt.executeQuery(select * from dept); while (rs.next() System.out.println (rs.getStr
6、ing(deptno); System.out.println (rs.getInt(deptno); System.out.println (rs.getString(dname); rs.close(); /也报异常 stmt.close(); conn.close(); :中间出现异常 close() 都不会执行了: import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.State
7、ment; public class TestJDBC public static void main(String args) Connection conn = null; Statement stmt = null; ResultSet rs = null; try Class.forName(oracle.jdbc.driver.OracleDriver); conn = DriverManager.getConnection(jdbc:oracle:thin:localhost:1521:AOOPLE,scott,tiger); stmt = conn.createStatement
8、(); rs = stmt.executeQuery(select * from dept); while (rs.next() System.out.println(rs.getString(deptno); System.out.println(rs.getString(dname); catch (ClassNotFoundException e) e.printStackTrace(); System.out.println(no qudong); catch(SQLException e) e.printStackTrace(); finally try if (rs != null
9、) rs.close(); rs = null; if (stmt != null) stmt.close(); stmt = null; if (conn != null) conn.close(); conn = null; catch (SQLException e) e.printStackTrace(); /异常放log文件里 ,/加注释; select id,count(*) from tablename group by id having count(*) 0SELECT COUNT(U.ID)FROM T_USER U, T_ROLE R, T_USER_ROLE URWHE
10、RE U.ID = UR.USER_ID AND R.ID = UR.ROLE_ID GROUP BY R.ID;试一下nvl()SELECT nvl(COUNT(U.ID) ,0)FROM T_USER U, T_ROLE R, T_USER_ROLE UR WHERE U.ID = UR.USER_ID AND R.ID = UR.ROLE_ID GROUP BY R.ID帮忙看一下这段分月统计的SQL楼主 发表于 2003-10-17 17:47 | 只看该作者 | 正序看帖 | 打印 下面这段SQL语句 的作用是统计出分月收入,然后求出每月所占的比例。我总觉得这样写有点问题,请问各位高
11、手帮忙指点一下!还有就是怎么求每月的收入增长率(本月收入-上月收入)/上月收入)?SQL Select c.月份,c.收入合计, round(c.收入合计/d.全年合计)*100,2) As所占比例2From3(Selectto_char(a.日期,MM)|月 As 月份, Sum(a.实收金额) As 收入合计4From 病人费用汇总a5Group By to_char(a.日期,MM)|月 )C,6(Select 全年总计 As 月份,Sum(b.实收金额) As 全年合计7From 病人费用汇总 b)D;月份 收入合计 所占比例- - -07月502587.53 26.7408月 110
12、5787.96 58.8309月271370.07 14.44ORACLE817 完全恢复手记!谢谢海龙的点拨,下面是一个SQL语句:Select c.月份,c.收入合计, (c.收入合计/d.全年合计)*100 As所占比例,(c.收入合计-(LAG(c.收入合计,1,c.收入合计) over (order by c.月份)/(LAG(c.收入合计,1,c.收入合计) over (order by c.月份)*100 as 收入增长率From(Selectto_char(a.日期,YYYYmm)|月 As 月份, Sum(a.实收金额) As 收入合计 From 病人费用汇总awhere a
13、.日期 between 0 and 1Group By to_char(a.日期,YYYYmm)|月 )C, (Select Sum(b.实收金额) As 全年合计 From 病人费用汇总 b where b.日期 between 0 and 1)D对不起,5楼的帖子修改一下!不用建表,直接在查询上面再套一层就行,select sum(所占比例) from (Select c.月份,c.收入合计, round(c.收入合计/d.全年合计)*100,2) As所占比例 From (Selectto_char(a.日期,MM)|月 As 月份, Sum(a.实收金额) As 收入合计 From 病
14、人费用汇总a Group By to_char(a.日期,MM)|月 )C, (Select 全年总计 As 月份,Sum(b.实收金额) As 全年合计 From 病人费用汇总 b);!6楼的结果中序号为9的数据中99.99是什么?我测试过了没有出现问题哦。SQL Select c.月份,c.收入合计, round(c.收入合计/d.全年合计)*100,2) As所占比例2From3(Selectto_char(a.日期,MM)|月 As 月份, Sum(a.实收金额) As 收入合计4From 病人费用汇总a5Group By to_char(a.日期,MM)|月 )C,6(Select
15、全年总计 As 月份,Sum(b.实收金额) As 全年合计7From 病人费用汇总 b)D;结果:序号 月份 收入合计 所占比例1 03月 167636.8 1.192 04月 3141242.26 22.313 05月 1270952.78 9.034 06月 1508083 10.715 07月 2571222.91 18.266 08月 2981140.64 21.187 09月 2226222.6 15.818 10月 211329 1.59 合计 14077829.99 99.99select sum(实收金额) as 合计from 病人费用汇总;结果:合计14077829.99o
16、racle 只根据年份(如2011)查询每月统计数据 检举 | 2011-8-17 00:01 提问者:sat111 | 浏览次数:359次问题补充: 我需要只根据年份查询每月统计数据,如 select to_char(时间,yyyy-mm) as 每月时间, sum(统计数据) from 测试表 where 时间= time group by 每月时间注意只有一个传入参数 time ,比如time值 为 2011/7/1 18:42:43 就查询出2011年第一天到最后一天的数据,不管time 的 月份 日期 为多少 就只查询该年份第一天到最后一天的数据求解决方法select to_char
17、(时间,yyyy-mm) as 每月时间, sum(统计数据) from 测试表 where to_char(时间),YYYY-MM-DD)like 2011% group by to_char(时间,yyyy-mm)select to_char(时间,yyyy-mm) as 每月时间, sum(统计数据) from 测试表 where to_char(时间,YYYY)= to_char(time,YYYY) group by 每月时间 赞同0| 评论 检举 | 2011-8-17 10:04 wangzhiqing999 | 十三级 where TRUNC( 时间, YYYY ) = TRU
18、NC( time , YYYY ) 赞同0| 评论 检举 | 2011-8-17 10:11 98稻草人 | 二级 不知道你的时间和参数time分别是什么类型的,只能暂时考虑为vchar型的。select to_char(时间,yyyy-mm) as 每月时间, sum(统计数据)from 测试表where substr(to_char(to_date(2011/7,YYYY/MM),YYYY/MM/DD),0,4) = substr(to_char(to_timestamp(2011/7/1 18:42:43,YYYY/MM/DD hh24:mi:ss),YYYY/MM/DD hh24:mi
19、:ss),0,4)group by to_char(时间,yyyy-mm)-下面的这个假设时间是date型的,参数time是timestamp型select to_char(时间,yyyy-mm) as 每月时间, sum(统计数据)from 测试表where substr(to_char(时间,yyyy-mm),0,4) = substr(to_char(time,YYYY/MM/DD hh24:mi:ss),0,4)group by to_char(时间,yyyy-mm)oracle的按月统计sql(一个数据库面试题) 收藏 题目:两个数据库表,schema如下:CREATE TABLE
20、REG_USERS(ID NUMBER,NAME VARCHAR2(30 BYTE),CREATE_DATE DATE -注册日期)CREATE TABLE SAMENAME_USERS(ID NUMBER,NAME VARCHAR2(30 BYTE)要求:1 统计出每个月的注册人数;2 列出用户名相同的所有记录;3 将用户名相同的记录插入SAMENAME_USERS表;将以下记录插入表TABLE REG_USERS:Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (2, lisa, TO_DATE(10/27/2009 23:53:58
21、, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (12, sd, TO_DATE(05/27/2009 00:04:00, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (13, lisi, TO_DATE(08/27/2009 02:02:00, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Va
22、lues (23, skote, TO_DATE(08/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (1, zhangsan, TO_DATE(09/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (3, wangwu, TO_DATE(10/27/2009 23:52:58, MM/DD/YYYY HH24:MI:S
23、S);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (4, zhanglong, TO_DATE(08/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (5, zhaohu, TO_DATE(10/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (6, wangch
24、ao, TO_DATE(07/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (7, mahan, TO_DATE(10/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (8, zhanzhao, TO_DATE(07/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into
25、 REG_USERS (ID, NAME, CREATE_DATE)Values (9, jojoy, TO_DATE(10/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (10, lisi, TO_DATE(10/27/2009 23:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (11, sd, TO_DATE(10/27/2009 2
26、3:52:58, MM/DD/YYYY HH24:MI:SS);Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (22, lisi, TO_DATE(07/27/2009 23:53:25, MM/DD/YYYY HH24:MI:SS);COMMIT;解题答案:1 统计出每个月的注册人数SELECT create_month|月 as month,(SELECT COUNT (*) FROM reg_users WHERE TO_CHAR (create_date, MM) = t1.create_month) total FROM (S
27、ELECT DISTINCT TO_CHAR (create_date, MM) create_month FROM reg_users ORDER BY create_month) t1;MON TOTAL- -05月 1 07月 3 08月 3 09月 1 10月 7 5 rows selected.*20091105补充* 以上解法其实不严谨,只是按月进行了统计,未对年进行统计,比如再执行以下SQL插入一条记录:Insert into REG_USERS (ID, NAME, CREATE_DATE)Values (22, lisi, TO_DATE(10/27/2008 23:53:2
28、5, MM/DD/YYYY HH24:MI:SS); 即插入一条2008年10月注册的记录,若用以上解法则10月的注册人数统计TOTAL为8,这样就有点不符合题意,正确的结果应该将数据按年月进行统计,SQL如下:select to_char(create_date,yyyy-MM),count(create_date) from reg_users group by to_char(create_date,yyyy-MM);执行结果为:MON TOTAL- -2009-05 1 2009-07 3 2009-08 3 2009-09 1 2009-10 72008-10 1 6 rows se
29、lected.2 列出用户名相同的记录 SELECT * FROM reg_users WHERE NAME IN (SELECT NAME FROM reg_users GROUP BY NAME HAVING COUNT (NAME) 1); ID NAME CREATE_DA- - - 13 lisi 27-八月-09 10 lisi 27-十月-09 22 lisi 27-七月-09 12 sd 27-五月-09 11 sd 27-十月-095 rows selected. 3 将用户名相同的记录插入samename_users表INSERT INTO samename_users(I
30、D, NAME) SELECT ID, NAME FROM reg_users WHERE NAME IN (SELECT NAME FROM reg_users GROUP BY NAME HAVING COUNT (NAME) 1);SELECT * FROM samename_users; ID NAME - - 13 lisi 10 lisi 22 lisi 12 sd 11 sd 5 rows selected.收集oracle统计信息优化器统计范围:表统计; -行数,块数,行平均长度;all_tables:NUM_ROWS,BLOCKS,AVG_ROW_LEN;列统计; -列中唯一
31、值的数量(NDV),NULL值的数量,数据分布; -DBA_TAB_COLUMNS:NUM_DISTINCT,NUM_NULLS,HISTOGRAM;索引统计;-叶块数量,等级,聚簇因子; -DBA_INDEXES:LEAF_BLOCKS,CLUSTERING_FACTOR,BLEVEL;系统统计;-I/O性能与使用率; -CPU性能与使用率; -存储在aux_stats$中,需要使用dbms_stats收集,I/O统计在X$KCFIO中;-analyze-需要使用ANALYZE统计的统计:使用LIST CHAINED ROWS和VALIDATE子句;收集空闲列表块的统计;Analyze ta
32、ble tablename compute statistics;Analyze index|cluster indexname estimate statistics;ANALYZE TABLE tablename COMPUTE STATISTICSFOR TABLEFOR ALL LOCAL INDEXESFOR ALL INDEXED COLUMNS;ANALYZE TABLE tablename DELETE STATISTICSANALYZE TABLE tablename VALIDATE REF UPDATEANALYZE TABLE tablename VALIDATE ST
33、RUCTURE CASCADE|INTO TableNameANALYZE TABLE tablename LIST CHAINED ROWS INTO TableNameANALYZE 不适合做分区表的分析-dbms_stats-dbms_stats能良好地估计统计数据(尤其是针对较大的分区表),并能获得更好的统计结果,最终制定出速度更快的SQL执行计划。这个包的下面四个存储过程分别收集index、table、schema、database的统计信息:dbms_stats.gather_table_stats 收集表、列和索引的统计信息;dbms_stats.gather_schema_st
34、ats 收集SCHEMA下所有对象的统计信息;dbms_stats.gather_index_stats 收集索引的统计信息;dbms_stats.gather_system_stats 收集系统统计信息dbms_stats.GATHER_DICTIONARY_STATS: 所有字典对象的统计;DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计dbms_stats.delete_table_stats 删除表的统计信息dbms_stats.delete_index_stats 删除索引的统计信息dbms_stats.export_table_stat
35、s 输出表的统计信息dbms_stats.create_state_tabledbms_stats.set_table_stats 设置表的统计dbms_stats.auto_sample_size统计收集的权限=必须授予普通用户权限sysORADB grant execute_catalog_role to hr;sysORADB grant connect,resource,analyze any to hr;统计收集的时间考虑=当参数STATISTICS_LEVEL设置为TYPICAL或者ALL,系统会在夜间自动收集统计信息。查看系统自动收集统计信息的job:SELECT * FROM dba_scheduler_jobs WHERE job_name = GATHER_STATS_JOB;也可以disable自动收集统计信息:BEGINdbms_scheduler.disable(GATHER_STATS_JOB);END;使用手工统计对所有更改活动中等的对象自动统计应该足够充分,由于自动统计收集在夜间进行,因此对于一些更新频繁的对象其统计可能已经过期。两种典型的对象:高度变化的表在白天的活动期间被TRUNCATE/DROP并重建;块加载超过本身总大小10%的对象;对于第一种对象可以使用以下两种方法:1 将这些
限制150内