<p>嗯,很难在评论中提供例子,我决定发表一个答案:</p>
<p>如果必须遵循原始数据,则无法区分第一个结构和第二个结构的数据。应该有一个附加的分组条件来删除第一个和第二个结构之间的方向。在</p>
<pre><code>sqlite> create table atoms (id INT, atom TEXT, amino TEXT);
sqlite> insert into atoms VALUES (1, 'O', 'HOH');
sqlite> insert into atoms VALUES (2, 'A', 'ARG');
sqlite> insert into atoms VALUES (3, 'B', 'CYS');
sqlite> insert into atoms VALUES (4, 'C', 'SER');
sqlite> insert into atoms VALUES (5, 'D', 'ARG');
sqlite> insert into atoms VALUES (6, 'O1', 'HOH');
sqlite> insert into atoms VALUES (7, 'A1', 'ARG');
sqlite> insert into atoms VALUES (8, 'B1', 'CYS');
sqlite> insert into atoms VALUES (9, 'C1', 'SER');
sqlite> insert into atoms VALUES (10, 'D1', 'ARG');
sqlite> select * from atoms;
1|O|HOH
2|A|ARG
3|B|CYS
4|C|SER
5|D|ARG
6|O1|HOH
7|A1|ARG
8|B1|CYS
9|C1|SER
10|D1|ARG
</code></pre>
<p><strong></p>
<p>以下是原始数据:</p>
^{pr2}$
<p>问题如下:</p>
<pre><code>select
c1.donor_id, c2.acceptor_id, 'W' as directness
from
contacts c1, contacts c2, atoms a
where
c1.acceptor_id = c2.donor_id
and c1.acceptor_id=a.id
and a.amino='HOH'
UNION ALL
select
c1.donor_id, c2.donor_id, 'X' as directness
from
contacts c1, contacts c2, atoms a
where
c1.acceptor_id = c2.acceptor_id
and c1.acceptor_id=a.id
and a.amino='HOH'
and c1.donor_id < c2.donor_id
UNION ALL
select
c1.acceptor_id, c2.acceptor_id, 'X' as directness
from
contacts c1, contacts c2, atoms a
where
c1.donor_id = c2.donor_id
and c1.donor_id=a.id
and a.amino='HOH'
and c1.acceptor_id < c2.acceptor_id;
</code></pre>
<p>结果如下:</p>
<pre><code>donor_id acceptor_id directness
-
2 4 W
2 5 W
3 4 W
3 5 W
7 9 W
7 10 W
8 9 W
8 10 W
2 3 X
7 8 X
4 5 X
9 10 X
</code></pre>