在同一张表中进行SQL查询

Question

我有一个新手级别的与SQL相关的问题。

假设我有这个简单的表：

A      B
------ ------
a      b
a      c
b      a
m      n
m      o
n      m

我想查询只有“对应项”的记录，即仅当表中存在b a时，才获取a b，但我想跳过“反向链接”（这里是b a）。 总之，我想得到以下结果。

A       B
------  ------
a       b
m       n

这个 SQL 查询不起作用，因为在这种情况下，处理 b a 时， a b 会从我的结果集中移除。

SELECT DISTINCT x1.A, x1.B
FROM TEST x1, TEST x2
WHERE x1.A = x2.B AND x1.B = x2.A -- all records /w counterparts only
AND x1.A NOT IN (SELECT B from TEST where B = x1.A) -- skip the "back links"

“WHERE”子句的第二部分不能按预期工作。

你有什么提示吗？非常感谢你的任何帮助。

问候 彼得

附言：我正在使用Derby数据库。

- D. Manka

---

b a 是 a b 的反向链接。同样，a b 也是 b a 的反向链接。你想选择保留哪一个并跳过哪一个？ - fthiella

但是如何知道什么是正确的答案（顺序）？为什么不是b a和n m，而是a b和m n？ - Justin

Answer 1

您可以将最后一行改为：

您可以将最后一行修改为：

AND x1.A < x1.B

假设您的列不会自我引用（例如a,a），或者您不希望出现循环引用。如果您需要循环引用，则需按如下方式操作：

AND x1.A <= x1.B

更新：

你最好也使用显式连接：

SELECT DISTINCT 
    x1.A
    , x1.B
FROM 
    TEST x1
JOIN 
    TEST x2
    ON x1.A = x2.B 
        AND x1.B = x2.A -- all records /w counterparts only
WHERE x1.A < x1.B --Skip backreference

- JAQFrost

---

这是最优雅的解决方案，我想...它像魅力一样工作...非常感谢Jason！ :) - D. Manka

Answer 2

SELECT Distinct
  case when tab1.A < tab1.B then tab1.A else tab1.B end as A,
  case when tab1.A > tab1.B then tab1.A else tab1.B end as B
FROM
  tab inner join tab tab1 on tab.B = tab1.A
WHERE
  tab1.B = tab.A

编辑：根据您更新的答案，我认为您需要这个：

select distinct
  (case when tab1.A < tab1.B then tab1.A else tab1.B end) as A,
  (case when tab1.A > tab1.B then tab1.A else tab1.B end) as B
from TEST tab1 left join TEST tab2 on tab1.B = tab2.A

它的结果与您的查询相同，但由于没有笛卡尔积，因此速度更快。

- fthiella

---

@P.Pete 谢谢... 我必须承认你的被采纳答案比我的更优雅 :) - fthiella

Answer 3

我没有Derby数据库可以测试，但我认为这应该可以工作。
由于您没有指定如何确定a/b和b/a之间的后向链接，因此我使用了第一个找到的正确方向。

查询背后的思想是将相同的表连接在一起以获取后向链接和表示找到的项目位置的行号。然后使用位置将表连接在一起，并选择找到的第一个出现的。

select TOT1.A, TOT2.B
(select distinct t1.A, t1.B, row_number() over() as num
from test t1
join test t2
on t1.A = t2.B and t1.B = t2.A) as TOT1
join
(select distinct t1.A, t1.B, row_number() over() as num
from test t1
join test t2
on t1.A = t2.B and t1.B = t2.A) as TOT2
on TOT1.A = TOT2.B and TOT1.B = TOT2.A and TOT1.NUM < TOT2.NUM

- il_guru

---

好的，这似乎是一个解决方案。你的解决方案不错，但它并不独立于数据库系统。例如，MySQL不提供row_number()函数。不过，有一些变通方法。 - D. Manka

您指定了您正在使用DerbyDb，因此我将提供使用DerbyDb功能的解决方案。 - il_guru

Answer 4

感谢迄今为止所有的答案

我有一个稍微修改过（更简单）的第一个问题版本。 我不需要检查“对应”的行，我只需要跳过反向引用。

我到目前为止修改了fthiellas的解决方案（见下文），它可以工作。不知何故，我认为一定有一个更简单的解决方案。

select distinct
( case when tab1.A < tab1.B then tab1.A else tab1.B end ) as A,
( case when tab1.A > tab1.B then tab1.A else tab1.B end) as B
from TEST tab1, TEST tab2

由于我不受Derby的限制，我想知道在切换到另一个数据库系统时，CASE函数是否会出现问题。最好有一种“通用”解决方案，适用于所有不同的SQL方言（如MySQL、Postgres、Oracle、MSSQL等）。

有什么想法吗？

- D. Manka

---

看我的更新答案...它会给你相同的结果，但比你的查询快得多。当时标准SQL，因此应该适用于大多数DBMS。 - fthiella

Answer 5

SELECT *
FROM ztable t
WHERE EXISTS (
    SELECT * FROM ztable x
    WHERE x.a = t.b AND x.b = t.a
    AND x.a > x.b -- tie breaker
    );

exists 有一个优点，那就是 (相关的) 子查询不会对外层查询可见; 因此 select * 只会展开表 t 的列。

- wildplasser

Answer 6

为什么你需要tab2呢？ 这个方案可以根据你的期望结果工作。

select 
  distinct tab1.ID, 
  case when tab1.A < tab1.B then tab1.A else tab1.B end as A,
  case when tab1.A > tab1.B then tab1.A else tab1.B end as B  
from TEST tab1;

- Brahmnik

Answer 7

以下是创建我的表格以及插入数据的 SQL 代码

CREATE TABLE TEST (A varchar(4), B varchar(4));
INSERT INTO TEST (ID,A,B) VALUES ('1','d','a');
INSERT INTO TEST (ID,A,B) VALUES ('1','c','a');
INSERT INTO TEST (ID,A,B) VALUES ('1','b','a');
INSERT INTO TEST (ID,A,B) VALUES ('1','a','xxx');
INSERT INTO TEST (ID,A,B) VALUES ('1','a','d');
INSERT INTO TEST (ID,A,B) VALUES ('1','a','c');
INSERT INTO TEST (ID,A,B) VALUES ('1','a','b');
INSERT INTO TEST (ID,A,B) VALUES ('2','g','a');
INSERT INTO TEST (ID,A,B) VALUES ('2','a','g');
INSERT INTO TEST (ID,A,B) VALUES ('3','f','b');
INSERT INTO TEST (ID,A,B) VALUES ('3','b','f');
INSERT INTO TEST (ID,A,B) VALUES ('4','s','r');
INSERT INTO TEST (ID,A,B) VALUES ('4','r','s');
INSERT INTO TEST (ID,A,B) VALUES ('5','r','t');
INSERT INTO TEST (ID,A,B) VALUES ('7','h','g');

如之前所述，使用以下查询：

select distinct tab1.ID,
   ( case when tab1.A < tab1.B then tab1.A else tab1.B end ) as A,
   ( case when tab1.A > tab1.B then tab1.A else tab1.B end) as B
from TEST tab1, TEST tab2

...我得到了期望的结果：

ID  A  B
--  -- --
1   a  b
1   a  c
1   a  d
1   a  xxx
2   a  g
3   b  f
4   r  s
5   r  t
7   g  h

抱歉，伙计们，也许我错过了什么，但似乎你们的解决方案仍然不能按预期工作。

@fthiella：我测试了你的解决方案：

SELECT tab1.*
FROM TEST tab1 LEFT JOIN TEST tab2 on tab1.B=tab2.A
WHERE tab1.A<tab1.B OR tab2.A is null

结果（a/b 重复，g/h 缺失）:

ID  A  B
--  -- --
1   a  b
1   a  b
1   a  c
1   a  xxx
2   a  g
3   b  f
4   r  s
5   r  t

@wildplasser：看起来这个解决方案也不起作用

SELECT * FROM TEST t
WHERE EXISTS (
    SELECT * FROM TEST x
    WHERE x.a = t.b AND x.b = t.a
    AND x.a > x.b -- tie breaker
    );

结果（a/xxx和r/t缺失）：

ID  A  B
--  -- --
1   a  b
1   a  c
1   a  d
2   a  g
3   b  f
4   r  s

- D. Manka

---

我又编辑了一下我的答案，现在应该和你的查询完全相同，只是更快，因为没有笛卡尔积。 - fthiella