postgreSQL:如何在一个结果中选择一次2列的值

Question

我有：

ID  USER1  USER2  TEXT
=======================
1   Toto    Roro  Text1
2   Toto    Riri  Text2
3   Tata    Roro  Text3
4   Titi    Rara  Text4
5   Tutu    Riri  Text5

我想要：

ID  USER1   USER2  TEXT
=======================-
1   Toto    Roro  Text1
4   Titi    Rara  Text4
5   Tutu    Riri  Text5

我希望USER1和USER2在我的结果中只出现一次，并按id asc排序。

我可以用子查询做很多不同的事情，但我认为它没有优化。。。

例如，我也希望将resultat的数量限制为10，因为我的表中有很多行。

我有这样的东西：

SELECT *
FROM (
    SELECT DISTINCT ON (user2) *
    FROM (
        SELECT DISTINCT ON (user1) *
        FROM (
            SELECT *
            FROM
                test
            ORDER BY
                id ASC
            LIMIT 100
        ) as foo1
        ORDER BY user1, id ASC
        LIMIT 100
    ) as foo2
    ORDER BY user2, id ASC
    LIMIT 100
) as foo3
ORDER BY id ASC
LIMIT 10

Answer 1

    Select * from table t1
    Where not exists (select 1 from table t2
                      where t2.id<t1.id 
                      and (t1.user1 in (t2.user1, t2.user2) 
                          or t1.user2 in (t2.user1, t2.user2)
                          )
                      )

不得不在我的笔记本电脑上编辑。我想这就是你的意思。你想把结果的数量限制在10个，然后把前10个放在选择中，还是我误解了？

Answer 2

明白了！http://sqlfiddle.com/#！2/f543b/148

select sj1.uid as uid,
sj1.u1 as u1,
sj2.u1 as u2,
j.text
from jargon as j
    ,(select j1.user1 as u1,
    j1.user2 as u2,
    min(j1.id) as uid,
    count(j1.user1)
    from jargon as j1
    group by j1.user1) as sj1
    ,(select j2.user2 as u1,
    count(j2.user2)
    from jargon as j2
    group by j2.user2) as sj2
where sj1.u2 = sj2.u1
and j.id = sj1.uid
group by sj2.u1
having min(sj1.uid)
order by sj1.uid asc

这将返回第1、4、5行，但对于如此小的数据集，我不能确定这是否适用于整个数据集。我有一个更大的查询，它很可能与更大的数据集一起工作，但它相当未优化。