I开发者_如何学Python had this query:
SELECT `name`, floor(max(score)), skill
FROM
(SELECT k.`name`, s.`name` as skill, *long complex formula* as score
FROM `keywords_skills` ks
JOIN keywords k ON k.id = ks.keyword_id
JOIN skills s ON s.id = ks.skill_id
JOIN jobs_keywords jk ON jk.keyword_id = k.id
WHERE job_id = 87293) t1
GROUP BY `name`
ORDER BY `name` asc
obviously i want 'skill' to refer the same row as max(score), but I did not know how to make that happen. However, when I add an ORDER BY
to the subquery like so:
SELECT `name`, floor(max(score)), skill
FROM
(SELECT k.`name`, s.`name` as skill, *long complex formula* as score
FROM `keywords_skills` ks
JOIN keywords k ON k.id = ks.keyword_id
JOIN skills s ON s.id = ks.skill_id
JOIN jobs_keywords jk ON jk.keyword_id = k.id
WHERE job_id = 87293
ORDER BY score DESC) t1
GROUP BY `name`
ORDER BY `name` asc
everything seems to work great! My question is: have I solved my problem or just implemented an unreliable hack that will haunt me later?
EDIT: perhaps I should have explain more what I was looking for:
Keywords and Skills are in a many-to-many relationship with each other. I am not looking simply for the keyword with the highest score, but the skill with the highest score for each keyword.
I also thought I could use LIMIT 1
somehow (perhaps in a subquery), but so far have not thought of a way to make it happen.
I think you are close with your first one... just add a LIMIT 1 in addition to the order by to put the highest skill first (order by column 3 which is your score formula
-- PER REVISED CLARIFICATION... I'm swapping around the query in hierarchy of the JOB ID in question, then finding ITs skills and keywords instead of the reverse -- relying on keyword skills. In the case you want, you WILL need a nested query... Example:
Job A has Keywords
Word A
Skill A-1 Score:90 (you want this one)
Skill A-2 Score:70
Skill A-3 Score:60
Word B
Skill B-1 Score:30
Skill B-2 Score:20
Skill B-3 Score:95 (you want this one)
Skill B-4 Score:80
Word C
Skill C-1 Score:10
Skill C-2 Score:20
Skill C-3 Score:30 (you want this one)
The inner query should ONLY include the keyword id and the maximum score for the keyword -- all associated with the one Job_ID in questino. THEN, re-join again, but this time, we no longer need he job_keywords as each K.ID and name description are IN the prequery. Then, just need to re-join to the skills matching the qualifying SCORE.
SELECT STRAIGHT_JOIN
PreQuery.Keyword,
s.name as Skill,
PreQuery.Score
from
( SELECT STRAIGHT_JOIN
k.id,
k.name Keyword,
max( s.Score ) maxScore
from
jobs_keywords jk
join keywords k
on jk.keyword_id = k.id
join keyword_skills ks
on k.id = ks.keyword_id
join skills s
on ks.skill_id = s.id
where
jk.job_id = 87293
group by
k.id,
k.name ) PreQuery
join keyword_skills ks
on PreQuery.id = ks.keyword_id
join skills s
on ks.skill_id = s.id
AND PreQuery.maxScore = s.Score
order by
PreQuery.Keyword
精彩评论