连接结果中的唯一行

pbgvytdp 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(373)

我有一张table delas 以及 curencies 像这样吗

curecnies
id,code

pairs (the available pairs of curencies )
id  to_sell to_buy

deals 
id
user_id
pair_id
amount_to_sell
amount_to_buy

所以我需要得到所有可以执行的匹配交易，但是我不能得到唯一的匹配。
这是我的sql查询

select *
from deals as d1
join deals d2
    on d1.sell_amount = d2.buy_amount and d1.buy_amount = d2.sell_amount

我得到的结果看起来像这个id | user | id | pair | id | amount | to | buy | amount | to | sell | id | user | id | pair | amount to | buy | amount to | sell

1|2|1|1000|3000|2|1|2|3000|1000
2|1|2|3000|1000|1|2|1|1000|3000

mysql Join unique

来源：https://stackoverflow.com/questions/49982938/unique-rows-in-join-result

1条答案

按热度按时间

fv2wmkja1#

您可以尝试在此处使用最小/最大的技巧：

SELECT t1.*, t2.*
FROM
(
    SELECT DISTINCT
        LEAST(d1.id, d2.id) AS d1_id,
        GREATEST(d1.id, d2.id) AS d2_id
    FROM deals AS d1
    INNER JOIN deals d2
        ON d1.sell_amount = d2.buy_amount AND
           d1.buy_amount = d2.sell_amount
) d
INNER JOIN deals t1
    ON d.d1_id = t1.id
INNER JOIN deals t2
    ON d.d2_id = t2.id;

这里的基本思想是子查询 d 使用最小/最大技巧查找一对匹配的交易ID。然后，我们两次加入 deals 再次输入表以输入该交易对中每个成员的完整信息。

赞(0）回复(0）举报 2021-06-21

我来回答

连接结果中的唯一行

1条答案

相关问题

热门标签

最新问答