oracle计算相差的小事,oracle 学会怎么样分解复杂的问题

//问题

表TEST:

ID 姓名 消费 日期 退休标志(1-在岗,2-退休)

004 张三 20 2010-05-11 1

007 李四 50 2010-06-23 1

002 王五 10 2010-06-23 2

004 张三 100 2010-07-20 2

//按退休标志分组，统计消费人次和金额，结果为：

退休标志 消费人次 消费

1 1 50

2 3 130

//注：张三在2010-07-20已退休，之前的在岗消费则以退休来统计人次和消费金额

//解法：

//问题分析：

//如果此题只是统计在岗和退休的人员的话，会是一个简单的查询语句：

select 退休标志 flag,count(*) cnt,sum(消费) amount

from test

group by 退休标志;

//但是这里有一个关键问题就是，同一个人在不同的时间段的退休标志不一样，

//也就是以前在岗，后来退休了;

//刚刚碰到此问题的时候，或许没有什么思路，对于处理同一个人，在不同时间段的退休标志不一;

//其实有了上面那个查询的基础，我们就可以解决此问题了；

//我们所要做的就是将表中所有退休的人员的退休标志修改为2即可；

//这么一来我们就要用到内嵌视图：

//下面我们就来修改一下已经退休了的人员的退休标志：

//这里所说的修改，不是update表，而是做一个标志:

with test as (

select '004' id,'张三' name,20 amount,date'2010-05-11' pdate,1 flag from dual union all

select '007','李四',50,date'2010-06-23',1 from dual union all

select '002','王五',10,date'2010-06-23',2 from dual union all

select '004','张三',100,date'2010-07-20',2 from dual)

select t.*,

case

when (select count(*)

from test a

where a.id=t.id

and a.flag=2)>0

then 2

else 1

end newflag

from test t

/

ID NAME AMOUNT PDATE FLAG NEWFLAG

--- ---- ---------- ----------- ---------- ----------

004 张三 20 2010-05-11 1 2

007 李四 50 2010-06-23 1 1

002 王五 10 2010-06-23 2 2

004 张三 100 2010-07-20 2 2

//上面这个case就是将已退休人员的退休前标志从1标记为2;

//里层的when部分使用到了一个表自连接的查询：

select a.id,count(*) cnt

from test a,test b

where a.id=b.id

and a.flag=2

group by a.id

/

ID CNT

--- ----------

002 1

004 2

//这个子查询就是为了将已退休人员的id号找出来,

//并在case语句中，将这些id号为已退休的退休标志设置为2，

//这就是上面那个查询里面我们看到的newflag

//然后作为新的退休标志，用于主查询中

//最后，我们来看看怎么构造最开始提出的那个简单的查询:

with test as (

select '004' id,'张三' name,20 amount,date'2010-05-11' pdate,1 flag from dual union all

select '007','李四',50,date'2010-06-23',1 from dual union all

select '002','王五',10,date'2010-06-23',2 from dual union all

select '004','张三',100,date'2010-07-20',2 from dual)

select newflag flag, count(*) cnt, sum(amount) amount

from (select t.*,

case

when (select count(*)

from test a

where a.id = t.id

and a.flag = 2) > 0

then 2

else 1

end newflag

from test t)

group by newflag

/

FLAG CNT AMOUNT

---------- ---------- ----------

1 1 50

2 3 130

//小结:

//碰到问题，要细心的分析，竟可能的将问题分解，也就是将大事化小，小事化了，

//最后，在将这些分解了的小问题综合起来，就可以解决大问题了