懂数据库的大V们快来帮帮忙吧！（已解决，谢谢各位！）

[ceres]

不懂数据库不是大V
select * from table_name
where table_name.ENTITY1_ID<table_name.ENTITY2_ID
你说的很含混，不如把原题贴上来。只从这8行记录，基于数据的局限性，可以有N种不同的做法。但在假设不成立的条件下，几乎不能说出那个是你需要的答案。要知道，用汉语，而且是模棱两可的汉语随意表达的数据库查询要求，是会导致混乱的答案的。

上面就是一种，但是几乎可以肯定太局限于这8行数据，不是你想要的，也不是你老师想要的答案。

你想要只保留正向反向的一个关系，但即使这样表达，依然还有很多事情没说清楚。

这不是一道作业题，客户只给了这几行，但从这几行我发现他们的关系是双向的，而我之能用单向。

 

[一亩三分地]

这不是一道作业题，客户只给了这几行，但从这几行我发现他们的关系是双向的，而我之能用单向。

a. create temporary table to store entity1_id and entity2_id only when possible.
b. iterating through all entries in the original table and skip anything that causes problem.
c. select * from orig_table where (orig_table.entity1_id,orig_table. entity2_id) IN (select tmp_table.entity1_id, tmp_table.entity2_id from tmp_table);

 

[ccc]

庆幸自己当初没学这东西。

 

[乐极生悲]

庆幸自己当初没学这东西。

想起来了，你昨天说你在南安普吨读过书，为啥又跑大家拿来了呢？

 

[ccc]

想起来了，你昨天说你在南安普吨读过书，为啥又跑大家拿来了呢？

哪里来的都有吧。

怎么混都是混。

 

[乐极生悲]

哪里来的都有吧。

怎么混都是混。

别，俺就羡慕你们这种在英国贵族学校里混过的

 

[ceres]

庆幸自己当初没学这东西。

这东西有意思着呢。

 

[一亩三分地]

庆幸自己当初没学这东西。

鸡肋

 

[ceres]

a. create temporary table to store entity1_id and entity2_id only when possible.
b. iterating through all entries in the original table and skip anything that causes problem.
c. select * from orig_table where (orig_table.entity1_id,orig_table. entity2_id) IN (select tmp_table.entity1_id, tmp_table.entity2_id from tmp_table);

这个跟我以前的想法差不多，刚刚跟同事咨询了一下，有另外一种做法，觉得还不错：get entity1_id and entity2_id by using an entity1_role ,ie using 'husband' only for husband and wife relationship, 'father' for father and daughter/son relationship....。

 

[gumpgump]

这个跟我以前的想法差不多，刚刚跟同事咨询了一下，有另外一种做法，觉得还不错：get entity1_id and entity2_id by using an entity1_role ,ie using 'husband' only for husband and wife relationship, 'father' for father and daughter/son relationship....。

Using "'father' for father and daughter/son relationship" would cause missing either father/daughter or father/son relationship in your result set. Originally, you wanted to keep each existing relationship once and eliminate the redundant one. Remember that? I assume you wanna keep both Father/daughter and Father/son relationships because they were not duplicated relationships. If you use only one single 'Father' record for father and daughter/son relationship, you will keep the daughter's ENTITY_ID or the son's ENTITY_ID? Either way, you will miss a relationship, either the Father/daughter or the Father/son relationship.

Standard SQL statement should be described accurately via E-R relationship and exact requirement instead of imagined by a general expression in natural language. Some idea would be confusing/specious, such as "'father' for father and daughter/son relationship" etc. You even did not recognize that it cannot be implemented correctly with "'husband' only for husband and wife relationship" together in one single SQL statement. In addition, how do you deal with the "'Mother' for mother and daughter/son relationship"?(Same issue here)

The only issue you are facing is the doubled relationships in the table, and your goal simply is deleting one of them no matter which one would be deleted. There is no need to think about it by enumerating all kinds of relationships between people.The thing you wanna do is ensuring the original dataset is really doubled/redundant as described in the 8 records, and simply deleting one of the pair of relationships. When struggling or being attracted by guess in natural language, you will be lost in the way you are thinking.

 

[ceres]

Gumpgump,
Thanks for your mention, 8 rows is a sample, but I just realize this table does not hold too much data, so I can ask for whole data and delete the duplicate ones.

 

[piaop]

不用这么麻烦吧
select * from relationship_table
minus
select * from relationship_table where (entity1_id, entity1_role) in
(
select distinct a, b
from (
select entity1_id a, entity1_role b from relationship_table
union
select entity2_id a, entity2_role b from relationship_table
)
group by a, b
having count(*)>1
)

 

[好玩儿]

尼玛，这么简单的问题，cursor 和 iteration都出来了。都是些什么人在coding啊，怪不得卖热狗都在CFC上狂扁coder。
两种办法，用union或者full join。楼主不用谢，实在不好意思要谢，把CFC币都给我吧。

   select *
   from orig
   where entity1_id<=entity2_id
   union corr
   select
      entity2_id as entity1_id,
      entity2_role as entity1_role,
      entity1_id as entity2_id,
      entity1_role as entity2_role
   from orig
   where entity1_id>entity2_id;

   select
      coalesce(a.entity1_id,b.entity2_id) as entity1_id,
      coalesce(a.entity1_role,b.entity2_role) as entity1_role,
      coalesce(a.entity2_id,b.entity1_id) as entity2_id,
      coalesce(a.entity2_role,b.entity1_role) as entity2_role
   from
      orig as a
      full join
      orig as b
      on a.entity1_id=b.entity2_id
   where a.entity1_id<=a.entity2_id and b.entity1_id>b.entity2_id;

如果你能保证所有数据都是双向的（都有两行，其中一行交换1和2后等于另一行），更简单，一条代码就出来了

   select *
   from orig
   where entity1_id<=entity2_id

 

[ceres]

尼玛，这么简单的问题，cursor 和 iteration都出来了。都是些什么人在coding啊，怪不得卖热狗都在CFC上狂扁coder。
两种办法，用union或者full join。楼主不用谢，实在不好意思要谢，把CFC币都给我吧。

   select *
   from orig
   where entity1_id<=entity2_id
   union corr
   select
      entity2_id as entity1_id,
      entity2_role as entity1_role,
      entity1_id as entity2_id,
      entity1_role as entity2_role
   from orig
   where entity1_id>entity2_id;

   select
      coalesce(a.entity1_id,b.entity2_id) as entity1_id,
      coalesce(a.entity1_role,b.entity2_role) as entity1_role,
      coalesce(a.entity2_id,b.entity1_id) as entity2_id,
      coalesce(a.entity2_role,b.entity1_role) as entity2_role
   from
      orig as a
      full join
      orig as b
      on a.entity1_id=b.entity2_id
   where a.entity1_id<=a.entity2_id and b.entity1_id>b.entity2_id;

如果你能保证所有数据都是双向的（都有两行，其中一行交换1和2后等于另一行），更简单，一条代码就出来了

   select *
   from orig
   where entity1_id<=entity2_id

看来你真是大V，一题三解，code 也是run过的。要钱没问题，反正没多少，不过得先回答我另外一个问题。也是client提供的。在database diagram 里，有的column name 旁边有像(IE2.4)的东东，这个是啥？

 

[好玩儿]

看来你真是大V，一题三解，code 也是run过的。要钱没问题，反正没多少，不过得先回答我另外一个问题。也是client提供的。在database diagram 里，有的column name 旁边有像(IE2.4)的东东，这个是啥？

脸皮真够厚的啊。算了，谁叫我聪明绝顶又爱装叉呢。那个是database的name，用的时候要用这样[IE2.4].
钱拿来。