mysql - 我怎样才能合并这个 mySQL 查询

Question

合并以下 mySQL 查询的最佳方式应该是什么？它应该变得复杂吗？我想像第一个查询一样按 match_score 对结果进行排序。我听说有一个联合选项可以连接多个选择查询，不确定它是如何工作的，但是有没有办法将 3 个查询合并到一个选择查询下？感谢您抽出时间。

查询 1:

SELECT 
    ui.User_ID,
    TRUNCATE(COUNT(*) / 2, 1) + COUNT(my.interest_id) * 2.5 AS match_score
FROM
    User_Interests ui
        LEFT JOIN
    User_Interests my ON (my.user_id = ?
        AND my.interest_id = ui.interest_id)
GROUP BY ui.user_id
ORDER BY match_score DESC

查询 2:

SELECT 
    ud.User_ID,
    TRUNCATE(COUNT(*) / 2, 1) + COUNT(my.dislike_id) * 2.5 AS match_score
FROM
    User_Dislikes ud
        LEFT JOIN
    User_Dislikes my ON (my.user_id = ?
        AND my.dislike_id = ud.dislike_id)
GROUP BY ud.user_id
ORDER BY match_score DESC

查询 3:

SELECT 
    u.*
FROM
    User u
        LEFT JOIN
    User_Status us ON u.id = User_ID1 AND us.User_ID2 = 5
WHERE
    gender = ?
        AND (us.Status != 'FRIENDS'
        OR us.status IS NULL)
        AND u.birthday >= DATE(NOW()) - INTERVAL ? YEAR - INTERVAL 1 YEAR
        AND u.birthday < DATE(NOW()) - INTERVAL ? YEAR

Answer 1

你说你想要添加分数。好的。您还说查询 3 用于过滤，但由于外连接不会自然过滤任何内容，因此唯一的过滤器是性别和出生日期。但是，您似乎也在尝试过滤用户 5 的非好友。我想您正在寻找这样的东西:

select 
  u.*, 
  i.match_score as interests_match_score,
  d.match_score as dislikes_match_score,
  coalesce(i.match_score, 0) + coalesce(d.match_score, 0) as total_match_score
from user u
left join
(
  select 
    ui.user_id,
    truncate(count(*) / 2, 1) + count(my.interest_id) * 2.5 as match_score
  from user_interests ui
  left join user_interests my on (my.user_id = ? and my.interest_id = ui.interest_id)
  group by ui.user_id
) i on i.user_id = u.id
left join
(
  select 
    ud.user_id,
    truncate(count(*) / 2, 1) + count(my.dislike_id) * 2.5 as match_score
  from user_dislikes ud
  left join user_dislikes my on (my.user_id = ? and my.dislike_id = ud.dislike_id)
  group by ud.user_id
) d on d.user_id = u.id
where id in (select user_id1 from user_status where user_id2 = 5 and status <> 'friends')
and gender = ?
and u.birthday >= date(now()) - interval ? year - interval 1 year
and u.birthday < date(now()) - interval ? year;

您可能需要进行修改，但这些应该很容易。