问题描述
我不知道标题有什么好处，但是我正在尝试找出一个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数据库中的对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！