mysql - 如何将反馈分数整合到搜索结果中

我当前正在运行以下查询，该查询返回一组作业以及它们的类别和发布人员的用户名。

SELECT job_id, user_id, title, profiles.user_name
FROM (jobs)
JOIN profiles ON jobs.user_id = profiles.user_id
JOIN job_categories ON jobs.cat_id = job_categories.cat_id
JOIN job_sub_categories ON jobs.sub_cat_id = job_sub_categories.sub_cat_id
WHERE `status` = 'open'
ORDER BY post_date desc
LIMIT 5

我有一个称为“反馈”的表，该表根据特定雇主的先前交易（类似于ebay）保存一排反馈。

feedback_id|employer_id|job_id|performance_score|quality_score|availability_score|communication_score

我想要做的是根据雇主当前的反馈等级对结果进行排序和过滤，我不确定如何将其添加到查询中。看来我必须在查询中进行一些数学运算还是运行子查询？还是应该修改我的反馈表以包括其他字段，例如针对特定评分给出的总反馈？

任何帮助将不胜感激。

评分是根据所有反馈评分相加得出的，然后除以行数，然后除以4，因为存在4个评分字段（性能，质量，可用性和沟通能力），因此feedback_avg = (feedback_total/num_rows)/4

最佳答案

让我试一下。我将假设您只有两个表employers: [id, name]和feedback: [id, employer_id, score]。

首先，得分子查询：

SELECT employer_id, SUM(score) AS total_score, COUNT(*) AS num_rows
       FROM feedback GROUP BY employer_id;

现在主要查询：

SELECT name, total_score/num_rows AS avg_score
       FROM employers JOIN ([subquery]) AS sq ON(employers.id = sq.employer_id)
       WHERE avg_score > 0.5;

将整个子查询粘贴到指示的位置。



提示：观看次数

如果愿意，可以使子查询成为永久视图，并在主查询中使用该视图：

CREATE VIEW score_tally AS
SELECT employer_id, SUM(score) AS total_score, COUNT(*) AS num_rows
       FROM feedback
       GROUP BY employer_id;

SELECT name, total_score/num_rows AS avg_score
       FROM employers JOIN score_tally ON(employers.id = score_tally.employer_id)
       WHERE avg_score > 0.5;

提示（再次）：上面的提示很愚蠢，我们应该使用内置的AVG：

CREATE VIEW score_tally AS
SELECT employer_id, AVG(score) AS avg_score
       FROM feedback
       GROUP BY employer_id;

SELECT name, avg_score
       FROM employers JOIN score_tally ON(employers.id = score_tally.employer_id)
       WHERE avg_score > 0.5;

让我们猜测一下完整的查询可能是什么样的：

SELECT job_id,
       user_id,
       title,
       profiles.user_name AS user_name,
       avg_score

       FROM jobs
            JOIN profiles ON(jobs.user_id = profiles.user_id)
            JOIN job_categories ON(jobs.cat_id = job_categories.cat_id)
            JOIN job_sub_categories ON(jobs.sub_cat_id = job_sub_categories.sub_cat_id)
            JOIN (SELECT employer_id, AVG(score) AS avg_score FROM feedback GROUP BY employer_id) AS sq
                 ON(employers.id = sq.employer_id)

        WHERE status = 'open' AND avg_score > 0.5

        ORDER BY post_date desc
        LIMIT 5