oracle 用一条sql语句,查询同一个表用同一个字段的条件查同一个字段的结果

感谢
htl258
Tony
的无私帮助,解决了我数据库的设计缺陷的问题``

oracle数据库
表是这样的
表名:bookuse_book
表字段:
DATECREATED DATE 用来记录录入的时间
CONSUMPTION NUMBER 用来记金额
CONSUMPTIONCLASS VARCHAR2(200) 用来标记,这里的值是花了,则CONSUMPTION字段里的金额数字为花了的钱数,如果这里的值是赚了,则CONSUMPTION字段里的金额数字是赚了的值

需求是用一条sql语句,分别查出一个月内每天CONSUMPTION字段中花了的值和赚了的值

结果如下:

花了 赚了 日期
12 15 2009-1-1
140 190 2009-1-2
10 60 2009-1-3
0 60 2009-1-4
10 0 2009-1-5

上边值的解释,12,15,140,190,10,60,60,10都是CONSUMPTION字段里的值,需要用一条sql语句,把他们这个月属于花了还是赚了的值显示出来,不用计算每天花了或者赚了的总和,只要一条一条显示出来就可以


想要的结果

[img]/upload/attachment/85479/13657eb8-0778-3d74-8dc9-1a39471ba641.gif[/img]


在我数据库里执行出来的结果,和原始数据

[img]/upload/attachment/85482/a9dae986-da3d-3c03-b5e3-9f494dff8c51.gif[/img]


问题的解决:

下边的这个sql是根据月份,查出特定时间内,所有的花了和赚了的记录,不计算总和


select
(case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end) as 花了,
(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) as 赚了,
DATECREATED as 日期
from bookuse_book
where (case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end)+
(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) <>0
and DATECREATED between to_date('2009-03-13','yyyy-mm-dd') and to_date('2009-03-14','yyyy-mm-dd')
order by DATECREATED



下边的这个sql是查出这个表中所有的花了和赚了的记录,不计算总和
select 
(case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end) as 花了,
(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) as 赚了,
DATECREATED as 日期
from bookuse_book
where (case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end)+
(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) <>0
order by DATECREATED


下边的这个sql是查出这个表中所有的花了和赚了的记录,计算总和

select 
sum(case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end) as 花了,
sum(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) as 赚了,
DATECREATED as 日期
from bookuse_book
group by DATECREATED


下边的这个sql是根据月份,查出特定时间内,所有的花了和赚了的记录,计算总和

select 
sum(case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end) as 花了,
sum(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) as 赚了,
DATECREATED as 日期
from bookuse_book
where DATECREATED between to_date('2009-03-13','yyyy-mm-dd') and to_date('2009-03-14','yyyy-mm-dd')
group by DATECREATED


下边的这个sql是查出所有的花了和赚了的记录,不计算总和

select 
(case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end) as 花了,
(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) as 赚了,
DATECREATED as 日期
from bookuse_book
where (case CONSUMPTIONCLASS when '花了' then CONSUMPTION else 0 end)+
(case CONSUMPTIONCLASS when '赚了' then CONSUMPTION else 0 end) <>0
order by DATECREATED


黑色头发:http://heisetoufa.iteye.com/
展开阅读全文

没有更多推荐了,返回首页