匹配MySQL数据库中的对 [英] Match pairs in a MySQL database

问题描述

我不知道标题有什么好处，但是我正在尝试找出一个MySQL数据库的SELECT语句，该语句可以给我匹配的项目.我有两个桌子.

I don't know of the title is any good, but I'm trying to figure out a SELECT statement for a MySQL database that gives me matching items. I have two tables.

示例:

Offers
------
FK_User_ID | FK_Skill_ID | (other columns that are not relevant)
1            1
2            2

Requests
--------
FK_User_iD | FK_Skill_ID | (other columns that are not relevant)
1          | 2
2          | 1

你明白我的意思吗?用户1具有技能1并寻求技能2-用户2具有技能2并寻求技能1.我们有一场比赛！

You see what I mean? User 1 has skill 1 and seeks skill 2 -- User 2 has skill 2 and seeks skill 1. We have a match!

说我是用户1.如何获得与我的要约/请求匹配的用户和技能?

Say I'm user 1. How can I get the users and skills that have a Offer/Request match with me?

作为用户1，我想检索以下数据:

As user 1, I would like to retrieve the following data:

FK_User_ID | FK_Skill_ID | Type
2            1             Request
2            2             Offer

我希望我的问题很清楚.任何帮助，我们将不胜感激.

I hope my problem is clear. Any help is greatly appreciated.

更多详细信息:用户可以具有多种技能，并且可以要求多种技能.一场比赛并不意味着每个技能都需要被比赛，而是至少一项.我想知道哪一个.这只是关于匹配的技能.

More details: A user can have more than one skill and can request more than one skill. A match does not mean every skill needs to be matched, but at least one. And I would like to know which one then. It's only about skills that match.

推荐答案

SELECT FK_User_ID, FK_Skill_ID, 'Offer' AS Type FROM offers WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM requests WHERE FK_User_ID = 1)
UNION
SELECT FK_User_ID, FK_Skill_ID, 'Request' AS Type FROM requests WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM offers WHERE FK_User_ID = 1)

此查询将返回ID为1的用户的请求/要约列表.它将返回用户1提出的请求和用户1提出的请求.如果您可以如@Chris所建议的那样用有关数据的更多详细信息更新问题，那么我可能会有所帮助.

This query will return a list of requests/offers for user with ID 1. It will return all the offers user 1 has requests for and all requests user 1 has offers for. If you can update question with more details about your data, as @Chris suggested then I might be of more help.

更新:

如果您需要在请求和报价两者上都匹配，那么将需要做更多的工作.

If you need a match on both request and offer then it will be a bit more work.

SELECT matching_offers.*, 'Offer' as type FROM 
(SELECT FK_User_ID, FK_Skill_ID FROM offers WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM requests WHERE FK_User_ID = 1)) AS matching_offers INNER JOIN
    (SELECT FK_User_ID, FK_Skill_ID FROM requests WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM offers WHERE FK_User_ID = 1)) matching_requests ON matching_offers.FK_User_ID=matching_requests.FK_User_ID
UNION
SELECT matching_requests.*, 'Request' as type FROM 
(SELECT FK_User_ID, FK_Skill_ID FROM offers WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM requests WHERE FK_User_ID = 1)) AS matching_offers INNER JOIN
    (SELECT FK_User_ID, FK_Skill_ID FROM requests WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM offers WHERE FK_User_ID = 1)) matching_requests ON matching_offers.FK_User_ID=matching_requests.FK_User_ID

这将是非常低效的，但应该可以.也许有人谁的SQLfu比我的更好?

This will be very unefficient but should work. Maybe someone whos SQLfu is better than mine can improve it)

这篇关于匹配MySQL数据库中的对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！