将表中的行与同一表中的其他行配对？ [英] Pairing a row in a table with other row(s) in the same table?

问题描述

我需要创建一个视图，将具有此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

推荐答案

我不知道只在SQLite中用SQL做这个的方法。所以这是我能想到的最佳解决方案：



1.获取所有不同的entity1 / entity2-pairs：

I wouldn't know of a way to do this with SQL only in SQLite. So this is the best solution I can think of:

1. Get all different entity1/entity2-pairs:

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.

我认为这样的事情就是你所追求的：

I think something like this is what you're after:

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)

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