Oracle(二)

 

第一章 单表查询

 

1.1.简单条件查询

1.精确查询 

需求：查询水表编号为30408的业主记录

查询语句：

select * from T_OWNERS where watermeter='30408'

查询结果：

2.模糊查询

需求：查询业主名称包含“刘”的业主记录

查询语句：

select * from t_owners where name like '%刘%'

查询结果：

3.  and运算符

需求：查询业主名称包含“刘”的并且门牌号包含5的业主记录

查询语句：

select * from t_owners where name like '%刘%' and housenumber like '%5%'

查询结果：

4.  or运算符

需求：查询业主名称包含“刘”的或者门牌号包含5的业主记录

查询语句：

select * from t_owners where name like '%刘%' or housenumber like '%5%'

查询结果：

 

5.  and 与or运算符混合使用

需求：查询业主名称包含“刘”的或者门牌号包含5的业主记录，并且地址编号为1的记录。

语句：

select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=1

查询结果：

因为and的优先级比or大，所以我们需要用 (  ) 来改变优先级。

 

6.  范围查询

需求：查询台账记录中用水字数大于等于10000，并且小于等于20000的记录

我们可以用>= 和<=来实现，语句
 

select * from T_ACCOUNT  where usenum>=10000 and usenum<=20000

我们也可以用between   ..  and   ..来实现

select * from T_ACCOUNT  where usenum between 10000   and  20000

 

7.  空值查询

需求：查询T_PRICETABLE表中MAXNUM为空的记录

语句：

select * from T_PRICETABLE t where maxnum is null

查询结果：

需求：查询T_PRICETABLE表中MAXNUM不为空的记录

语句：

select * from T_PRICETABLE t where maxnum  is not null

查询结果：

 

1.2.去掉重复记录

需求：查询业主表中的地址ID,不重复显示

语句：

select distinct addressid from T_OWNERS   select distinct addressid， ownertypeid from T_OWNERS

 

1.3.排序查询

1.升序排序

需求：对T_ACCOUNT表按使用量进行升序排序

语句：

select * from T_ACCOUNT order by usenum

查询结果：

 

2.降序排序

需求：对T_ACCOUNT表按使用量进行降序排序

语句：

select * from T_ACCOUNT order by usenum desc

查询结果：

 

1.4.基于伪列的查询

在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：ROWID和ROWNUM。

1     ROWID
表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。

查询语句：

select rowID,t.* from T_AREA t

查询结果如下：

我们可以通过指定ROWID来查询记录

select rowID,t.* from T_AREA t where ROWID='AAAM1uAAGAAAAD8AAC';

查询结果如下：

 

2      ROWNUM
在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。

查询语句：

select rownum,t.* from T_OWNERTYPE t

查询结果如下：

 

 

1.5.聚合统计

ORACLE的聚合统计是通过分组函数来实现的，与MYSQL一致。

1. 聚合函数

（1）求和  sum

需求：统计2012年所有用户的用水量总和

select sum(usenum) from t_account where year='2012'

查询结果如下：

 

（2）求平均 avg

需求：统计2012年所有用水量（字数）的平均值

select avg(usenum) from T_ACCOUNT where year='2012'

查询结果如下：

 

（3）求最大值 max

需求：统计2012年最高用水量（字数）

select max(usenum) from T_ACCOUNT where year='2012'

查询结果如下：

 

（4）求最小值min

需求：统计2012年最低用水量（字数）

select min(usenum) from T_ACCOUNT where year='2012'

查询结果如下：

 

（5）统计记录个数 count

需求：统计业主类型ID为1的业主数量

select count(*) from T_OWNERS t where ownertypeid=1

查询结果如下：

 

2. 分组聚合Group by

需求：按区域分组统计水费合计数

语句：

select areaid,sum(money) from t_account group by areaid

查询结果：

 

3. 分组后条件查询 having

需求：查询水费合计大于20000的区域及水费合计

语句：

select areaid,sum(money)  from t_account group by areaid having sum(money)>20000

查询结果：

 

第二章 连接查询

1.多表内连接查询

（1）需求：查询显示业主编号，业主名称，业主类型名称，如下图：

查询语句：

select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id

（2）需求：查询显示业主编号，业主名称、地址名称和业主类型，如下图

 

分析：此查询需要三表关联查询。分别是业主表，业主分类表和地址表

语句：

select o.id 业主编号,o.name 业主名称,ad.name 地址, ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad where o.ownertypeid=ot.id and o.addressid=ad.id

(3) 需求：查询显示业主编号、业主名称、地址、所属区域、业主分类，如下图：

分析：这里需要四个表关联查询，比上边多了一个区域表（T_AREA）

查询语句：

select o.id 业主编号,o.name 业主名称,ar.name 区域,  ad.name 地址, ot.name 业主类型 from T_OWNERS o ,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA  ar where o.ownertypeid=ot.id and o.addressid=ad.id and ad.areaid=ar.id

(4) 需求：查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类，如下图：

 

分析：此查询比上边又多了一个表 T_OPERATOR

语句：

select ow.id 业主编号,ow.name 业主名称,ad.name 地址, ar.name 所属区域,op.name 收费员, ot.name 业主类型 from T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad , T_AREA ar,T_OPERATOR op where ow.ownertypeid=ot.id and ow.addressid=ad.id and ad.areaid=ar.id and ad.operatorid=op.id

 

2.外连接查询

A：左外连接查询

需求：查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名。

 

分析：我们要查询这个结果，需要用到T_OWNERS（业主表） ，T_ACCOUNT（台账表）  按照查询结果，业主表为左表、账务表为右表。

按照SQL1999标准的语法，查询语句如下：

SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow left join T_ACCOUNT ac on ow.id=ac.ownerid

按照ORACLE提供的语法，就很简单了：

SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow,T_ACCOUNT ac WHERE ow.id=ac.ownerid(+)

如果是左外连接，就在右表所在的条件一端填上（+）

 

B：右外连接查询

需求：查询所有地址表及业主记录，显示地址编号、地址、业主编号、业主信息。如果地址记录没有对应的业主信息，也要列出记录。如下图：

SQL1999标准的语句

select a.id, a.name, o.id, o.name  from T_OWNERS o right join T_ADDRESS a  on a.id = o.addressid

ORACLE的语法

select a.id, a.name, o.id, o.name  from T_OWNERS o, T_ADDRESS a  where a.id = o.addressid(+)

 

第三章 子查询

A:单行子查询

1. 只返回一条记录
2. 单行操作符

 

需求：查询2012年1月用水量大于平均值的台账记录

语句：

select * from T_ACCOUNT where year='2012' and month='01' and  usenum> ( select avg(usenum) from T_ACCOUNT where year='2012' and month='01' )

查询结果：

平均值为：

B:多行子查询

1. 返回了多条记录
2. 多行操作符

1、in 运算符

（1）需求：查询地址编号为1 、3、4 的业主记录

分析：如果我们用or运算符编写，SQL非常繁琐，所以我们用in来进行查询

语句如下：

select * from T_OWNERS where addressid in ( 1,3,4 )

查询结果如下：

（2）需求：查询地址含有“花园”的业主的信息

语句：

select * from T_OWNERS where addressid in ( select id from t_address where name like '%花园%' )

查询结果：

 

（3）需求：查询地址不含有“花园”的业主的信息

语句：

select * from T_OWNERS where addressid not in ( select id from t_address where name like '%花园%' )

查询结果：

2、all 运算符

需求：查询2012年台账中，使用量大于2012年3月最大使用量的台账数据

分析：此查询除了可以用max函数来实现查询

select * from T_ACCOUNT where usenum>(select max(usenum) from T_ACCOUNT where year='2012' and month='03')

除此之外，还可以使用all运算符来实现相同的效果，语句如下：

select * from T_ACCOUNT where year='2012' and usenum >all(select usenum from T_ACCOUNT WHERE year='2012' and month='03' )

查询结果如下：

 

第四章 分页查询

A:简单分页

需求：分页查询台账表T_ACCOUNT，每页10条记录

分析：我们在ORACLE进行分页查询，需要用到伪列ROWNUM和嵌套查询

我们首先显示前10条记录，语句如下：

select rownum,t.* from T_ACCOUNT t where rownum<=10

显示结果如下：

那么我们显示第11条到第20条的记录呢？编写语句：

select rownum,t.* from T_ACCOUNT t where rownum>10 and rownum<=20

查询结果：

嗯？怎么没有结果？

这是因为rownum是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。

那怎么办呢？我们可以使用子查询来实现

select * from (select rownum r,t.* from T_ACCOUNT t where rownum<=20) where r>10

 

查询结果如下：

B:基于排序的分页

需求：分页查询台账表T_ACCOUNT，每页10条记录，按使用字数降序排序，若使用字数相同按台账id编号升序排序。

我们查询第2页数据，如果基于上边的语句添加排序，语句如下：

select * from (select rownum r,t.* from T_ACCOUNT t where rownum<=20 order by usenum desc, id) where r>10

查询结果如下：

我们可以先排序查询

select rownum r,t.* from T_ACCOUNT t order by usenum desc,id

你会看到查询结果如下：

经过验证，我们看到第2页的结果应该是下列记录

所以推断刚才的语句是错误的！那为什么是错误的呢？

你会发现排序后的R是乱的。这是因为ROWNUM伪列的产生是在表记录扫描是产生的，而排序是后进行的，排序时R已经产生了，所以排序后R是乱的。

 

那该如何写呢？

很简单，我们只要再嵌套一层循环（一共三层），让结果先排序，然后对排序后的结果再产生R，这样就不会乱了。

语句如下：

select * from (select rownum r,t.* from    (select * from T_ACCOUNT order by usenum desc, id) t       where rownum<=20 ) where r>10

结果如下：

 

 

第五章 单行函数

5.1.字符函数

函    数	说               明
ASCII	返回对应字符的十进制值
CHR	给出十进制返回字符
CONCAT	拼接两个字符串，与 || 相同
INITCAT	将字符串的第一个字母变为大写
INSTR	找出某个字符串的位置
INSTRB	找出某个字符串的位置和字节数
LENGTH	以字符给出字符串的长度
LENGTHB	以字节给出字符串的长度
LOWER	将字符串转换成小写
LPAD	使用指定的字符在字符的左边填充
LTRIM	在左边裁剪掉指定的字符
RPAD	使用指定的字符在字符的右边填充
RTRIM	在右边裁剪掉指定的字符
REPLACE	执行字符串搜索和替换
SUBSTR	取字符串的子串
SUBSTRB	取字符串的子串（以字节）
SOUNDEX	返回一个同音字符串
TRANSLATE	执行字符串搜索和替换
TRIM	裁剪掉前面或后面的字符串
UPPER	将字符串变为大写

 

常用字符函数讲解：

（1）求字符串长度 LENGTH

语句：

select length('ABCD') from dual;

显示结果为：

 

（2）求字符串的子串SUBSTR

语句：

select substr('ABCD',2,2) from dual;

显示结果为：

 

(3) 字符串拼接CONCAT

语句：

select concat('ABC','D') from dual;

查询结果如下：

我们也可以用|| 对字符串进行拼接

select 'ABC'||'D' from dual;

查询结果同上。

 

 

5.2.数值函数

函数	说明
ABS(value)	绝对值
CEIL(value)	大于或等于value的最小整数
COS(value)	余弦
COSH(value)	反余弦
EXP(value)	e的value次幂
FLOOR(value)	小于或等于value的最大整数
LN(value)	value的自然对数
LOG(value)	value的以10为底的对数
MOD(value,divisor)	求模
POWER(value,exponent)	value的exponent次幂
ROUND(value,precision)	按precision 精度4舍5入
SIGN(value)	value为正返回1;为负返回-1;为0返回 0.
SIN(value)	余弦
SINH(value)	反余弦
SQRT(value)	value 的平方根
TAN(value)	正切
TANH(value)	反正切
TRUNC(value,按precision)	按照precision 截取value
VSIZE(value)	返回value在ORACLE的存储空间大小

常用数值函数讲解：

（1）四舍五入函数ROUND

语句：

select round(100.567) from dual

查询结果如下：

语句：

select round(100.567,2) from dual

查询结果如下：

 

(2) 截取函数TRUNC

语句：

select trunc(100.567) from dual

查询结果：

语句：

select trunc(100.567,2) from dual

 

 

(3) 取模  MOD

语句：

select mod(10,3) from dual

结果：

 

5.3.日期函数

函   数	描      述
ADD_MONTHS	在日期date上增加count个月
GREATEST(date1,date2,. . .)	从日期列表中选出最晚的日期
LAST_DAY( date )	返回日期date 所在月的最后一天
LEAST( date1, date2, . . .)	从日期列表中选出最早的日期
MONTHS_BETWEEN(date2,date1)	给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’)	给出日期date之后下一天的日期，这里的day为星期，如： MONDAY,Tuesday等。
NEW_TIME(date,’this’,’other’)	给出在this 时区=Other时区的日期和时间
ROUND(date,’format’)	未指定format时，如果日期中的时间在中午之前，则将日期中的时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。
TRUNC(date,’format’)	未指定format时，将日期截为12 A.M.( 午夜，一天的开始).

我们用sysdate这个系统变量来获取当前日期和时间

语句如下：

select sysdate from dual

查询结果如下：

 

常用日期函数讲解：

（1）加月函数 ADD_MONTHS ：在当前日期基础上加指定的月

语句：

select add_months(sysdate,2) from dual

查询结果如下：

 

(2) 求所在月最后一天LAST_DAY 

语句：

select last_day(sysdate) from dual

查询结果如下：

 

(3) 日期截取TRUNC

语句：

select TRUNC(sysdate) from dual

查询结果如下：

语句：

select TRUNC(sysdate,'yyyy') from dual

查询结果如下：

语句：

select TRUNC(sysdate,'mm') from dual

查询结果如下：

 

5.4.转换函数

函   数	描      述
CHARTOROWID	将 字符转换到 rowid类型
CONVERT	转换一个字符节到另外一个字符节
HEXTORAW	转换十六进制到raw 类型
RAWTOHEX	转换raw 到十六进制
ROWIDTOCHAR	转换 ROWID到字符
TO_CHAR	转换日期格式到字符串
TO_DATE	按照指定的格式将字符串转换到日期型
TO_MULTIBYTE	把单字节字符转换到多字节
TO_NUMBER	将数字字串转换到数字
TO_SINGLE_BYTE	转换多字节到单字节

 

常用转换函数讲解：

（1）数字转字符串TO_CHAR

语句：

select TO_CHAR(1024) from dual

查询结果：

 

（2）日期转字符串TO_CHAR

语句：

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

查询结果：

语句：

select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

查询结果：

（3）字符串转日期TO_DATE

语句：

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

查询结果如下：

（4）字符串转数字TO_NUMBER

语句：

select to_number('100') from dual

 

5.5.其它函数

（1）空值处理函数 NVL

用法：

NVL（检测的值，如果为null的值）；

语句：

select NVL(NULL,0) from dual

查询结果如下：

 

需求：

显示价格表中业主类型ID为1的价格记录，如果上限值为NULL,则显示9999999

语句：

select PRICE,MINNUM,NVL(MAXNUM,9999999) from T_PRICETABLE where OWNERTYPEID=1

查询结果：

 

（2）空值处理函数 NVL2

用法：

NVL2（检测的值，如果不为null的值，如果为null的值）；

需求：显示价格表中业主类型ID为1的价格记录，如果上限值为NULL,显示“不限”.

 

语句：

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限') from T_PRICETABLE where OWNERTYPEID=1

（3）条件取值 decode

语法：

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 【功能】根据条件返回相应值

需求：显示下列信息（不要关联查询业主类型表，直接判断1 2 3 的值）

 

语句：

select name,decode(  ownertypeid,1,'居民',2,'行政事业单位',3,'商业') as 类型   from T_OWNERS

 

上边的语句也可以用case when then 语句来实现

select name ,(case ownertypeid                   when 1 then '居民'                   when 2 then '行政事业单位'                   when 3 then '商业'                   else '其它'               end               ) from T_OWNERS

还有另外一种写法：

select name,(case                when ownertypeid= 1 then '居民'               when ownertypeid= 2 then '行政事业'               when ownertypeid= 3 then '商业'             end )             from T_OWNERS

 

第六章 行列转换

需求：按月份统计2012年各个地区的水费，如下图

select  (select name from T_AREA where id=  areaid ) 区域,    sum( case when month='01' then money else 0 end) 一月,    sum( case when month='02' then money else 0 end) 二月,    sum( case when month='03' then money else 0 end) 三月,    sum( case when month='04' then money else 0 end) 四月,    sum( case when month='05' then money else 0 end) 五月,    sum( case when month='06' then money else 0 end) 六月,    sum( case when month='07' then money else 0 end) 七月,    sum( case when month='08' then money else 0 end) 八月,    sum( case when month='09' then money else 0 end) 九月,    sum( case when month='10' then money else 0 end) 十月,    sum( case when month='11' then money else 0 end) 十一月,    sum( case when month='12' then money else 0 end) 十二月    from T_ACCOUNT where year='2012' group by areaid

 

需求：按季度统计2012年各个地区的水费，如下图

语句如下：

select  (select name from T_AREA where id=  areaid ) 区域,    sum( case when month>='01' and month<='03' then money else 0 end) 第一季度,    sum( case when month>='04' and month<='06' then money else 0 end) 第二季度,    sum( case when month>='07' and month<='09' then money else 0 end) 第三季度,    sum( case when month>='10' and month<='12' then money else 0 end) 第四季度    from T_ACCOUNT where year='2012' group by areaid

 

第七章 分析函数

以下三个分析函数可以用于排名使用。

下图为三种排名方式的举例

 

1. RANK    相同的值排名相同，排名跳跃

需求：对T_ACCOUNT表的usenum字段进行排序，相同的值排名相同，排名跳跃

语句：

select rank() over(order by usenum desc ),usenum from T_ACCOUNT

结果：

 

（2） DENSE_RANK   相同的值排名相同，排名连续

需求：对T_ACCOUNT表的usenum字段进行排序，相同的值排名相同，排名连续

语句：

select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT

结果：

 

（3）ROW_NUMBER   返回连续的排名，无论值是否相等

需求：对T_ACCOUNT表的usenum字段进行排序，返回连续的排名，无论值是否相等

语句：

select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

 

用row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:

select * from (select row_number() over(order by usenum desc ) rownumber,usenum from T_ACCOUNT) where rownumber>10 and rownumber<=20

查询结果如下：

 

（4）按组分析函数  over(partition by 列… order by ...)

需求: 查看账票记录的每个区域中2012年6月份水量使用最多的业主

select * from ( select rank() over(partition by ac.areaid order by ac.usenum desc ) rownumber, ac.areaid, ow.name, ac.usenum from T_ACCOUNT ac, t_owners ow where ac.ownerId = ow.id and year = 2012 and month = 6) where rownumber = 1

结果:

 

第八章 集合运算

A:什么是集合运算

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

··UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

··UNION(并集)，返回各个查询的所有记录，不包括重复记录。

··INTERSECT(交集)，返回两个查询共有的记录。

··MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

 

B:并集运算

 

UNION ALL 不去掉重复记录

select * from t_owners where id<=7 union all select * from t_owners where id>=5

 

结果如下：

 

UNION 去掉重复记录

select * from t_owners where id<=7 union select * from t_owners where id>=5

结果如下：

 

C:交集运算

select * from t_owners where id<=7 intersect select * from t_owners where id>=5

 

结果：

D:差集运算

select * from t_owners where id<=7 minus select * from t_owners where id>=5

结果:

 

如果我们用minus运算符来实现分页，语句如下：

select rownum,t.* from T_ACCOUNT t where rownum<=20 minus select rownum,t.* from T_ACCOUNT t where rownum<=10

 

 

第九章 综合案例

为《自来水收费系统》开发统计模块相关的功能

1.收费日报单（总）

统计某日的收费，按区域分组汇总，效果如下：

语句：

select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm-dd')='2012-02-28' group by areaid

 

2.收费日报单（收费员）

统计某收费员某日的收费，按区域分组汇总，效果如下：

语句：

select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm-dd')='2012-02-28' and feeuserid=2 group by areaid

 

3.收费月报表（总）

统计某年某月的收费记录，按区域分组汇总

语句:

select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm')='2012-05' group by areaid

 

4.收费月报表（收费员）

统计某收费员某年某月的收费记录，按区域分组汇总

语句：

select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy-mm')='2012-05' and feeuserid=2 group by areaid

 

5.收费年报表（分区域统计）

统计某年收费情况，按区域分组汇总，效果如下：

语句：

select (select name from T_AREA where id= areaid ) 区域, sum(usenum)/1000 "用水量(吨)" ,sum(money) 金额 from T_ACCOUNT where to_char(feedate,'yyyy')='2012' group by areaid

 

6.收费年报表（分月份统计）

统计某年收费情况，按月份分组汇总，效果如下

 

语句：

select to_char(feedate,'mm') 月份,sum(usenum)/1000 使用吨数,sum(money) 金额 from T_ACCOUNT where  to_char(feedate,'yyyy')='2012' GROUP BY to_char(feedate,'mm') ORDER BY to_char(feedate,'mm')

 

7.收费年报表（分月份统计）

统计某年收费情况，按月份分组汇总，效果如下

语句:

select '用水量(吨)' 统计项,       sum (case when to_char(feedate,'mm')='01'  then usenum/1000 else 0  end ) 一月,       sum (case when to_char(feedate,'mm')='02'  then usenum/1000 else 0  end ) 二月,       sum (case when to_char(feedate,'mm')='03'  then usenum/1000 else 0  end ) 三月,       sum (case when to_char(feedate,'mm')='04'  then usenum/1000 else 0  end ) 四月,       sum (case when to_char(feedate,'mm')='05'  then usenum/1000 else 0  end ) 五月,       sum (case when to_char(feedate,'mm')='06'  then usenum/1000 else 0  end ) 六月,       sum (case when to_char(feedate,'mm')='07'  then usenum/1000 else 0  end ) 七月,       sum (case when to_char(feedate,'mm')='08'  then usenum/1000 else 0  end ) 八月,       sum (case when to_char(feedate,'mm')='09'  then usenum/1000 else 0  end ) 九月,       sum (case when to_char(feedate,'mm')='10'  then usenum/1000 else 0  end ) 十月,       sum (case when to_char(feedate,'mm')='11'  then usenum/1000 else 0  end ) 十一月,       sum (case when to_char(feedate,'mm')='12'  then usenum/1000 else 0  end ) 十二月 from T_ACCOUNT where to_char(feedate,'yyyy')='2012' UNION ALL select '金额(元)' 统计项,       sum (case when to_char(feedate,'mm')='01'  then money else 0  end ) 一月,       sum (case when to_char(feedate,'mm')='02'  then money else 0  end ) 二月,       sum (case when to_char(feedate,'mm')='03'  then money else 0  end ) 三月,       sum (case when to_char(feedate,'mm')='04'  then money else 0  end ) 四月,       sum (case when to_char(feedate,'mm')='05'  then money else 0  end ) 五月,       sum (case when to_char(feedate,'mm')='06'  then money else 0  end ) 六月,       sum (case when to_char(feedate,'mm')='07'  then money else 0  end ) 七月,       sum (case when to_char(feedate,'mm')='08'  then money else 0  end ) 八月,       sum (case when to_char(feedate,'mm')='09'  then money else 0  end ) 九月,       sum (case when to_char(feedate,'mm')='10'  then money else 0  end ) 十月,       sum (case when to_char(feedate,'mm')='11'  then money else 0  end ) 十一月,       sum (case when to_char(feedate,'mm')='12'  then money else 0  end ) 十二月 from T_ACCOUNT where to_char(feedate,'yyyy')='2012'

 

8.统计用水量，收费金额（分类型统计）

根据业主类型分别统计每种居民的用水量（整数，四舍五入）及收费金额 ，如果该类型在台账表中无数据也需要列出值为0的记录 ， 效果如下：

语句：

select ow.name,  nvl( round(sum(usenum)/1000),0) "用水量(吨)" , nvl( sum(money),0) 金额 from T_OWNERTYPE ow ,T_ACCOUNT ac where ow.id=ac.ownertypeid(+)  group by ow.name

 

分析：这里所用到的知识点包括左外连接、sum()、分组group by 、round()  和nvl()

 

9.统计每个区域的业主户数，并列出合计

语句：

select ar.name 区域,count(ow.id) 业主户数 from T_AREA ar ,T_OWNERS ow,T_ADDRESS ad where ad.id=ow.addressid and ad.areaid=ar.id group by ar.name union all select '合计',count(1) from T_OWNERS

 

10.统计每个区域的业主户数，如果该区域没有业主户数也要列出0

如图:

语句：

select ar.name 区域,count(owad.id) 业主户数 from T_AREA ar , ( select ow.id,ow.name,ad.areaid from T_OWNERS ow,T_ADDRESS ad where ow.addressid=ad.id ) owad where ar.id=owad.areaid(+) group by ar.name