解答论坛上面的一个数据库问题

    在http://oracle.group.iteye.com/group/topic/19806上看到有人提出了一个数据库的问题，在上面看到了解答过程，有一个解答是用了表的关联（没有验证那个解答是否正确），其实不用表的关联，也可以达到效果。只用一个查询,再从结果集中获取数据，这样效率也会更高，因为毕竟只是对查询结果的计算，代价基本可以忽略。现把我的解答，及其分析过程写一下。

    题目：一个talbe  Material 它的列位有name,type, state,  type有A,B,C 它们都有三种state 有New Used Drop 分别用 100，200，300代表，material 是name 与type 确定唯一。比如 数据库中有以下数据

name	type	state
M1	A	100
M1	B	200
M1	C	300
M2	A	200
M2	B	100
M3	C	100
M3	A	200

 

 

    现在要查询出来的结果是 一个Name 就有且仅有一条记录 该记录包括它的所有type 的 状态  如：  

name AState BState CState M1 New Used Drop M2 Used New   M3 Used   New

 

 （1）这个需求里面有2个case when 的要求就是case name (case type when a )，但是数据库的case when 不支持嵌套的case when方式。这样就可以按照2步走，先用一个case when获取一个结果集，然后再在结果集中计算。

name a b c M1 100 200 300 M2 200 100 0 M3 200 0 100

 

 

能获取这个结果集，那么离上面的结果就不远了。

（2） 获取（1）中的结果集，首先我们获取一个简单点的结果集

name a b c M1 100     M1   200   M1     300 M2 200     M2   100   M3     100 M3 200

 

  获取这个结果集，就是说的第一个case when了，sql如下：

select name, 
case when type='A' then state else '' end as a,
case when type='B'then state else '' end as b, 
case when type='C' then state else '' end as c from material 

 

 

 问题是怎么样得到（1）中的结果集呢？我们应该注意到一个条件，material 是name 与type 是确定唯一，这就好办了，用sum就可以了.

select name, 
sum(case when type='A' then state else '' end) as a, 
sum(case when type='B'then state else '' end) as b, 
sum(case when type='C' then state else '' end) as c from material group by name 

 

这样我们就可以得到（1）中的结果集了。

 

（3）得到（1）中的结果，我们就完成了第一个case when ,我们再进行第二个case when 就可以得到所要的结果了。这个看看数据库文档里面的case when就可以明白下面这条sql了，就不多说了。

select t.name,
(case when t.a=100 then 'New' when t.a=200 then 'Used' when t.a=300 then 'Drop' else ''end) as AState, 
(case when t.b=100 then 'New' when t.b=200 then 'Used' when t.b=300 then 'Drop' else ''end) as BState, 
(case when t.c=100 then 'New' when t.c=200 then 'Used' when t.c=300 then 'Drop' else ''end) as CState 
from 
( select name, 
sum(case when type='A' then state else '' end) as a, 
sum(case when type='B'then state else '' end) as b,
 sum(case when type='C' then state else '' end) as c from material group by name ) t 

 

 

 这样我们就可以用一条sql，并且不用到任何自身关联，就可以得到结果集了。顺便说下，javaeye上面的文档编辑器，真的不好用。插入进去的都变形了，过会再调下。