返回JOIN和DISTINCT查询的过滤结果

Question

我有这两个表，我想查询一个唯一的记录：

create table active_pairs
(
id                    integer,
pair                  text,
exchange_id           integer
);

create table exchanges
(                        
exchange_id         integer,
exchange_full_name  text
);
INSERT INTO active_pairs (pair, exchange_id)
VALUES ('London/Berlin', 2),
('London/Berlin', 3),
('Paris/Berlin', 4),
('Paris/Berlin', 3),
('Oslo/Berlin', 2),
('Oslo/Berlin', 6),
('Huston/Berlin', 2);

INSERT INTO exchanges (exchange_id, exchange_full_name)
VALUES (2, 'Exchange 1'),
(3, 'Exchange 2'),
(4, 'Exchange 3'),
(3, 'Exchange 21'),
(2, 'Exchange 12'),
(6, 'Exchange 11'),
(2, 'Exchange 31');  

查询以列出只有一条pair记录的项目：

SELECT *  FROM active_pairs ap
INNER JOIN exchanges ce on ap.exchange_id = ce.exchange_id
WHERE ap.exchange_id = 2
GROUP BY pair, ap.exchange_id, ce.exchange_id, ap.id
HAVING COUNT(ap.pair) = 1
ORDER BY ap.pair

我也试过这个：

SELECT DISTINCT ON (ap.pair) ap.pair
FROM common.active_pairs ap
INNER JOIN common.exchanges ce on ap.exchange_id = ce.exchange_id
WHERE ce.exchange_id = 2

当我运行查询时，我没有得到正确的结果。我只需要获得Huston/Berlin，因为这是唯一的记录(注意，我们有另一个带有exchange_id = 2的记录(。现在我得到了结果Huston/Berlin和exchange_id = 2的"伦敦/柏林"，这是不正确的。

另一个例子：当我查询exchange_id=4时，我需要得到空的结果，因为正如您所看到的，我有用于exchange_id3和4的Paris/Berlin。

需要考虑的其他情况：当exchange_id表行为空时，我不应该得到exchange_id为空的行。

你能建议我如何解决这个问题吗？

Answer 1

不确定为什么需要来自common.exchanges的信息，但您可以执行以下操作：

SELECT pair
FROM active_pairs ap
WHERE NOT EXISTS (SELECT * FROM active_pairs other_pairing
WHERE other_pairing.pair = ap.pair
AND other_pairing.exchange_id != ap.exchange_id)
AND ap.exchange_id = 2
AND EXISTS (SELECT * FROM exchanges ep WHERE ep.exchange_id = ap.exchange_id)

因此，基本上，您在active_pairs中查找的任何记录在该表中都没有其他具有不同exchange_id的记录。

dbfiddle 上的工作演示

Answer 2

要只选择有一个且只有一个对的交换机，需要计算对的出现次数并根据该计数进行筛选。所以你似乎离解决方案很近了，所以这是你修改后的声明：

SELECT ce.exchange_id, count(ap.id) as pair_counter
FROM common.active_pairs ap
INNER JOIN common.exchanges ce on ap.exchange_id = ce.exchange_id
GROUP BY ce.exchange_id
HAVING count(ap.id)=1

你甚至不需要加入，只使用FK:会更快

SELECT ap.id, ap.exchange_id, count(ap.id) as pair_counter
FROM common.active_pairs ap
GROUP BY ap.id, ap.exchange_id
HAVING count(ap.id)=1