我有這兩個表，我想查詢：
create table active_pairs
(
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)
VALUES (2, 'Exchange 1'),
(3, 'Exchange 2'),
(4, 'Exchange 3'),
(3, 'Exchange 21'),
(2, 'Exchange 12'),
(6, 'Exchange 11'),
(2, 'Exchange 31');
我使用此查詢列出所有不同的對：
SELECT *
FROM common.active_pairs cp
GROUP BY pair, cp.exchange_id
HAVING COUNT(pair) = 1
ORDER BY :sort
我還需要列出exchange_full_name映射的值exchange_id
你能告訴我如何使用 JOIN 來解決這個問題嗎？
uj5u.com熱心網友回復：
你能解釋一下你需要什么嗎？如果您只需要查詢結果和交換之間的 JOIN，則可以使用如下子查詢：
WITH pairs AS(SELECT *
FROM active_pairs cp
GROUP BY pair, cp.exchange_id
HAVING COUNT(pair) = 1)
SELECT p.pair, p.exchange_id, e.exchange_full_name
FROM pairs p
INNER JOIN exchanges e ON(p.exchange_id = e.exchange_id);
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/432614.html
標籤：sql PostgreSQL