python sqlite3按列分组的多个左联接

Question

我试着看了所有的“重复”已经建议，但无法找到正确的查询。在

为了简单起见，我把我的问题转换成了另一个问题——所以我不是在寻找另一种结构数据的方法，而是一种根据现有模式查询数据的方法。在

复制问题和我尝试过的查询的代码位于here。在

给定以下模式

CREATE TABLE PERSONS(id, name, c1, c2);
CREATE TABLE FRIENDS(id, person_id, name, c1, c2);
CREATE TABLE ENEMIES(id, person_id, name, c1, c2);

以及一些示例数据（仅使用sqlite打印出来—我通过python进行代码通信）

^{pr2}$

我希望能够遍历persons表，并为每个人获取所有的朋友和敌人。（我希望在一个查询中这样做，因为我不想为每个人对朋友和敌人表进行多个查询）。在

p1 tom p1c1v p1c2v 
(some information about toms friends) together
[
    f1p1 "toms friend 1"
    f2p1 "toms friend 2"
    f3p1 "toms friend 3"
    f4p1 "toms friend 4"
]
(some information about toms enemies) togetner
[
    e1p1 "toms enemy 1"
    e2p1 "toms enemy 2"
    e3p1 "toms enemy 3"
    e4p1 "toms enemy 4"
]

如the code所示，我通过sqlite尝试了这些查询

SELECT p.id, p.name, f.id, f.person_id, f.name, e.id, e.person_id, e.name
FROM persons as p
LEFT JOIN friends as f on p.id = f.person_id
LEFT JOIN enemies as e on p.id = e.id
GROUP BY p.id

但是这个查询只为这个id返回一行，我不想要，我需要它的所有行。在

在删除组的时候，我确实得到了很多行，但它没有敌人的信息。在

本质上，我需要以下内容，但作为一个查询（除了添加索引外，不更改现有模式）

for person_row in "select * from persons"
    friend_rows_for_person = "select * from friends where person_id=person_row.id"
    enemies_rows_for_person = "select * from enemies where person_id=person_row.id"
    # I continue processing this person with friends 
    # and enemies before moving on to the next person

预期产量：

person_id    person_name    friend_id    friend_name      enemy_id    enemy_name
p1           tom            f1p1         toms friend 1    None        None
p1           tom            f2p1         toms friend 2    None        None
p1           tom            f3p1         toms friend 3    None        None
p1           tom            f4p1         toms friend 4    None        None
p1           tom            None         None             e1p1       toms enemy 1
p1           tom            None         None             e2p1       toms enemy 2
p1           tom            None         None             e3p1       toms enemy 3
p1           tom            None         NOne             e4p1       toms enemy 4
p2           sam            f1p2         sams friend 1    None        None
p2           sam            f2p2         sams friend 2    None        None
p2           sam            f3p2         sams friend 3    None        None
p2           sam            f4p2         sams friend 4    None        None
p2           sam            None         None             e1p2       sams enemy 1
p2           sam            None         None             e2p2       sams enemy 2
p2           sam            None         None             e3p2       sams enemy 3
p2           sam            None         None             e4p2       sams enemy 4
p3           tim            None         None             e1p3        tims enemy 1

Answer 1

这实际上是两个查询，合并为一个查询。 您需要使用compound query：

SELECT persons.id   AS person_id,
       persons.name AS person_name,
       friends.id   AS friend_id,
       friends.name AS friend_name,
       NULL         AS enemy_id,
       NULL         AS enemy_name,
       friends.id IS NULL    needed for sorting
FROM persons
JOIN friends ON persons.id = friends.person_id
UNION ALL
SELECT persons.id,
       persons.name,
       enemies.id,
       enemies.name,
       NULL,
       NULL,
       NULL
FROM persons
JOIN enemies ON persons.id = enemies.person_id
ORDER BY persons.id,
         friends.id IS NULL,
         friends.id,
         enemies.id;

（按friends.id IS NULL排序可确保非空友元行在空友元行之前排序。）