数据：
create table t2(id number,attr varchar2(10),val varchar2(10));

insert into t2 values(1,’地区’,'HZ’);
insert into t2 values(1,’区号’,'0572′);
insert into t2 values(1,’类别’,'医药’);

insert into t2 values(2,’传真’,'0588′);
insert into t2 values(2,’类别’,'IT’);

insert into t2 values(3,’类别’,'IT’);
insert into t2 values(3,’地区’,'BJ’);

insert into t2 values(4,’性别’,'男’);

需求：
每个id下面的attr是唯一的。数据量不大。

要求把   attr=’地区’ 且 val=’HZ’的
同时满足 attr=’类别’ 且 val=’IT’的
的id取出来，前提是有这样的attr。

如果这个id下面没有attr=’地区’ 或者没有 attr=’类别’的值，也需要被取出来。

返回的结果是：id=2,4

解决方案：
大家集思广益，最终觉得这个方案比较好。

select id from (
select id,
    max(case when attr=’地区’ then val else null end) t2,
    max(case when attr=’类别’ then val else null end) t3
    from t2
    group by id
) where (t2=’HZ’ or t2 is null)
and (t3=’IT’ or t3 is null) ;

这样再传一个attr进来，只需要增加：
max(case when attr=’xx’ then val else null end) t4
和
and (t4=’xx’ or t4 is null) ;

• 

  首先一点, 这种类似元数据的表设计 是一种极差的实践.
  bad practice.

  嘻嘻.

  木匠 @ February 1, 2008 |

• 

  有没有好的建议？

  grassbell @ February 1, 2008 |

• 

  我的建议,

  1) Normalization
  2) One Fact per Attribute (Attribute Disaggregation)

  当你的ERD实体关系设计好了以后, 会令你以后的数据维护和查询相当简单, 我们叫做: Elegant Design.

  建议你读一下 这本书:
  Data Modeling Essentials - Third Edition

  木匠 @ February 4, 2008 |

• 

  请这位木匠同学针对这个需求，结合你推荐的这部书，提出详细的设计方案，谢谢了。

  grassbell @ February 5, 2008 |

• 

  具体到细节, 是要收顾问费的, 嘻嘻.

  木匠 @ February 6, 2008 |

• 

  你的数据库最多仅需要四张表, 哈哈!

  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1460004570029#66768048838745
  Thanks for the question regarding “design the table”

  …
  We were also told to create the data model in such a way that the extraction program should be generic enough so that it can run for
  another client without any code change.
  ……

  hehehe, good luck with that. While you can do that, no one is going to be happy. Not you, not your management, and particularly not your end users.

  You already know my opinion on the matter. You have all of the details I’m willing to give. ‘Good luck’ is about the only thing I’ll add.

  This’ll be one of my items:

  http://tkyte.blogspot.com/2006/06/what-did-i-decide-on.html

  Just to give you a hint of things I might say about it:

  o make sure to only use varchar2(4000) for everything. It is after all the most flexible.

  o better yet, have a table that only has number columns - 1000 of them. These numbers will be
  surrogate keys that point to a table with a number (primary key) and a varchar2(4000). We all know that full scanning a wide table is bad, so the numbers will make it skinny to full scan (and a couple dozen joins to pick up the values should be super fast since we are using numbers - they join quick)

  o better better yet, a table with an “instance_id, attribute_id” and a number stored in a column called “value”. No longer need we be limited by the pesky 1,000 column limitation!! It’ll be very cool. You know you only need three tables:

  create table object ( instance_id, attribute_id, value );
  create table object_links (instance_id1, instance_id2 );
  create table attributes ( attribute_id, attribute_name, attribute_datatype );

  yeah, that’ll do it. Better yet, be sure to “normalize” the value out of object - adding a fourth table.

  Never create another table ever again!

  木匠 @ February 6, 2008 |

• 

  本木匠工人工作15年了, 叫咱工友, 听着更舒服.

  木匠 @ February 6, 2008 |

• 

  今天新学了一句话, 觉得用在这里比较合适.

  “Fixing design issues in SQL”

  http://www.youtube.com/watch?v=40Lnoyv-sXg

  木匠 @ February 6, 2008 |

• 

  发帖子的目的在于交流，汲取别人好的建议。看来在这位同学身上体现不了这个价值了。

  grassbell @ February 6, 2008 |

• 

  楼主最好的方法好像不行。 如果 把 要求把 第一个条件改成
  attr=’地区’ 且 val=’HZ’的
  同时满足 attr=’类别’ 且 val=’医药’ 改成这样。。ID为1的没有显示出来。。 彼人一好友经过努力得到最新代码：供参考：
  select distinct id from t2
  where id in(
  select a.id
  from t2 a,t2 b
  where a.id=b.id and (a.attr=’地区’ and a.val=’HZ’) and (b.attr=’类别’ and b.val=’医药’))
  or id not in(
  select a.id
  from t2 a,t2 b
  where a.id=b.id and a.attr=’地区’ and b.attr=’类别’)

  QQ:22793595 @ February 19, 2009 |

• 

  select id
  from t2
  group by id
  having sum( case when (attr=’地区’ and val=’HZ’)
  or (attr=’类别’ and val=’IT’) then 3
  when attr=’地区’ or attr=’类别’ then 2
  else 0
  end
  )=6
  or
  sum( case when (attr=’地区’ and val=’HZ’)
  or (attr=’类别’ and val=’IT’) then 3
  when attr=’地区’ or attr=’类别’ then 2
  else 0
  end
  )<4

  Jeff_dowa @ July 29, 2010 |

• 

  楼主的实现好像有点问题：
  ————————–
  如果这个id下面没有attr=’地区’或者没有 attr=’类别’的值，也需要被取出来
  insert into t2 values(2,’类别’,’IT’);
  将这条记录改成
  insert into t2 values(2,’类别’,’网吧’);
  则
  id=2 的集合满足 没有attr=’地区’的值，应该要包含到结果集中。
  ————————–
  楼主提供的SQL返回的结果集只有 id=4

  Jeff_dowa @ July 30, 2010 |