将表中的行与同一表中的其他行配对？

问题描述

我需要创建一个视图，将具有此DDL的表中的行配对：

I need to make a view that pairs rows in a table that has this DDL:

CREATE TABLE Events (
    source        TEXT,
    entity1       TEXT,
    entity2       TEXT,
    datetime      TEXT,
    backOdd1      TEXT NOT NULL,
    backOdd2      TEXT NOT NULL,
    insertionDate TEXT,
    PRIMARY KEY (
        source,
        entity1,
        entity2
    )
);

我目前在SQLite数据库中我对此失去了理智。我仍然没有数据库，甚至不知道从哪里开始。我曾多次尝试搞乱SELECT语句和INNER JOIN，我实际上并不知道要加入什么。如果有人能给我一个很棒的方向

Vague depiction of what I have in mind

I'm currently in an SQLite database and I'm losing my head over this. I'm still noobish with databases and don't even know where to begin with this. I tried several times messing with a SELECT statement and an INNER JOIN to which I don't actually know what to join to. If anyone could give me a direction that would be great

推荐答案

SELECT DISTINCT entity1, entity2 FROM Events;

2.在Java中过滤掉重复项（那些只按顺序不同的对）。



3.遍历其余对并查询匹配事件：

2. In Java filter out the duplicates (those pairs that only differ in order).

3. Loop through the remaining pairs and query the matching events:

SELECT * FROM Events WHERE (entity1=@e1 AND entity2=@e2) OR (entity1=@e2 AND entity2=@e1);

其中@ e1和@ e2是过滤后的实体对的参数名称。

Where @e1 and @e2 are the parameter names for the filtered entity pairs.

SELECT e1.source, e2.source FROM Events e1 INNER JOIN
(SELECT source FROM Events e2
ON e1.entity1=e2.entity1 AND e1.entity2 = e2.entity2 AND e1.source <> e2.Source)

这篇关于将表中的行与同一表中的其他行配对？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！