賞金將在 3 天后到期。此問題的答案有資格獲得 50聲望賞金。 user1285928正在從有信譽的來源尋找答案。
我有這兩個表，我想查詢一條唯一記錄：
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 = :exchangeId
GROUP BY pair, ap.exchange_id, ce.exchange_id, ap.id
HAVING COUNT(ap.pair) = 1
ORDER BY :sort
LIMIT :limit
OFFSET :offset
當我運行查詢時，我沒有得到正確的結果。我只需要獲取Huston/Berlin，因為這是唯一的記錄（注意我們有另一條記錄exchange_id = 2）。現在我進入結果Huston/Berlin和exchange_id = 2不正確的“倫敦/柏林”。
Another example: When I make query for exchange_id=4 I need to get empty result because as you can see I have Paris/Berlin for exchange_id 3 and 4.
Can you advice how I can fix this issue?
uj5u.com熱心網友回復：
沒有更多樣本來檢查結果，解決方案可能是這樣的：
SELECT ap.pair, ap.exchange_id, ce.exchange_id, ap.id FROM active_pairs ap
INNER JOIN exchanges ce on ap.exchange_id = ce.exchange_id
INNER JOIN (SELECT pair FROM active_pairs GROUP BY pair HAVING COUNT(pair) = 1) p on p.pair = ap.pair
WHERE ap.exchange_id = :exchangeId
GROUP BY pair, ap.exchange_id, ce.exchange_id, ap.id
ORDER BY :sort
LIMIT :limit
OFFSET :offset
我猜你只想要你給出的小例子中唯一的活動對名稱。
uj5u.com熱心網友回復：
如果我理解正確，這就是你想要做的：
select * from (
select *, count(*) over (partition by pair) as cc from active_pairs
) t join exchanges e on t.exchange_id = e.exchange_id and t.cc=1
db<>在這里擺弄
uj5u.com熱心網友回復：
SQL
SELECT ap.*, ce.* FROM active_pairs ap
INNER JOIN
(SELECT pair
FROM active_pairs
GROUP BY pair
HAVING COUNT(*) = 1) subq
ON ap.pair = subq.pair
INNER JOIN exchanges ce
ON ap.exchange_id = ce.exchange_id
WHERE ap.exchange_id = :exchangeId
ORDER BY :sort
LIMIT :limit
OFFSET :offset;
解釋
子查詢 ( subq) 過濾以僅包含出現一次的對名稱。然后根據您的原始查詢將其連接回active_pairs表以獲取exchange_id然后連接到交換表。
演示
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b3e2dcc5d09401e5bed1f42fdde82a6b
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/434311.html